MAX & IFs

[madbassist]

Dear Excel masters,

Column B contains numbers and Column C contains some data related to the Column B number in the same row. I want a formula that selects the maximum value from column and then "adds" (in the non-mathematical sense) the Column C data from the same row, IF it fulfils a certain criteria (I believe I have this part sorted)

i.e. if the max value is 35 and the cell next to it contains "n/o", I want the formula to give me 35n/o.

So far I have: =MAX(B4:B20)&IF(C4:C20="n/o","n/o",""), but this is not successful.

Thank you for your assistance,

Raju

[Bryan Hessey]

Dear Excel masters,

Column B contains numbers and Column C contains some data related to the Column B number in the same row. I want a formula that selects the maximum value from column and then "adds" (in the non-mathematical sense) the Column C data from the same row, IF it fulfils a certain criteria (I believe I have this part sorted)

i.e. if the max value is 35 and the cell next to it contains "n/o", I want the formula to give me 35n/o.

So far I have: =MAX(B4:B20)&IF(C4:C20="n/o","n/o",""), but this is not successful.

Thank you for your assistance,

Raju

Hi,

try

=MAX(B4:B20)&IF(INDIRECT("c"&3+MATCH(MAX(B4:B20),B4:B20,0))="n/o",INDIRECT("c"&3+MATCH(MAX(B4:B20),B4:B20,0)),"")

hth
---

[madbassist]

Thanks a lot!

[Bryan Hessey]

Thanks a lot!

np - and thanks for your response.

---