'IF' formulae to obtain different numbers in a cell, when another cell gives a specific value - please help...

[PJS_666]

=IF(AND(K29<215),"215",IF(AND(K29>215),"250",IF(AND(K29>251),"300",IF(AND(K29>301),"330",IF(AND(K29>331),"440"))))) -

I'm trying to generate a formulae to provide a number if is it firstly less than 215, you obtain 215, then numbers greater than 215, to provide, 250, 300,330 & 400 respectively. Unfortunately, the formulae only recognises the arguments up to IF(K29>215, 250).

Please help!!

Thanks

 

[Georgiboy]

Welcome to the forum, one option could be to reverse your logic:

=LET(r,I4,IFS(r>331,"440",r>301,"330",r>251,"300",r>215,"250",r<215,"215"))

 

[etaf]

yes - as any of the other numbers are >215
put them the other way round
=IF(AND(K29<215),"215",IF(AND(K29>215),"250",IF(AND(K29>251),"300",IF(AND(K29>301),"330",IF(AND(K29>331),"440"))))) -

=IF(AND(K29<215),"215",IF(AND(K29>331),"440",IF(AND(K29>301),"330",IF(AND(K29>251),"300",IF(AND(K29>215), 250)))))
note
"215" is not a number its text as you have put into "" if you want a number leave the "" OFF

=IF(AND(K29<215),215,IF(AND(K29>331),440,IF(AND(K29>301),330,IF(AND(K29>251),300,IF(AND(K29>215), 250,"")))))

 

[PJS_666]

yes - as any of the other numbers are >215
put them the other way round
=IF(AND(K29<215),"215",IF(AND(K29>215),"250",IF(AND(K29>251),"300",IF(AND(K29>301),"330",IF(AND(K29>331),"440"))))) -

=IF(AND(K29<215),"215",IF(AND(K29>331),"440",IF(AND(K29>301),"330",IF(AND(K29>251),"300",IF(AND(K29>215), 250)))))
note
"215" is not a number its text as you have put into "" if you want a number leave the "" OFF

=IF(AND(K29<215),215,IF(AND(K29>331),440,IF(AND(K29>301),330,IF(AND(K29>251),300,IF(AND(K29>215), 250,"")))))

Massive thank you!!

 

[kevin9999]

Another option:
=IFS(A1<215,215,A1>=331,440,A1>=301,330,A1>=251,300,A1>=215,250)