How to add multiple IF statements with conditions in Excel

Any idea how should I write query in excel for my below requirement. I have 2 columns. Column A having 5 cells and Column B having 5 cells.

1) If all cells in Column A contains text = "yes" then I would like to show status "Bronze" 2)If all cells in Column A contains text = "yes" AND all cells in column B = "yes" then I would like to show status "Gold" 3)If all cells in Column A = "yes" but any of the cell in Column B <> "yes" then I would like to show status "Silver" 4) If any of the cell in column A <> "yes" irrespective of status of cells in column B then I would like to show status "Others"

I tried simulating this with 2 cells in Column A and B with below,

=IF(AND(A18="yes",A19="yes"),"Bronze",IF(AND(A18="yes",A19="yes",B18="yes",B19="yes"),"Gold",IF(AND(A18="yes",A19="yes",B18="yes",B19="yes"),"Silver","Other")))

sorry I am not familiar with excel so can anyone help please ?