IFS formula
 

Hi. I've got to calculate multiple If on a formula, but I don't know what is wrong with my formula. Tks for answer.

Neither do we, because you did not explain what it is that you perceive to be "wrong".


Perhaps it is: your formula only returns 400 or "fără bursă". Your formula is:


=IFS(Q26>=8.5,400,Q26>=9,500,Q26>=9.5,600,Q26=10,1 000,Q26<=8.49,"fără bursă")


With Q26 = 9.03125, presumably you expect 500, not 400.


But when Q26>=9 is true, Q26>=8.5 is also true. And since IFS stops with the first true condition left-to-right, Q26>=8.5 will "win", and IFS will return 400.


You should re-order your conditions, to wit:


=IFS(Q26=10, 1000, Q26>=9.5, 600, Q26>=9, 500, Q26>=8.5, 400, TRUE, "fără bursă")


The use of TRUE obviates the need to test Q26<=8.49. And that is a good thing, because 8.49 is not the next value before 8.5, since you do not round the AVERAGE.



Alternatively:


=LOOKUP(Q26, {0, 8.5, 9, 9.5, 10}, {"fără bursă", 400, 500, 600, 1000})

Hi, your test order is not correct see attached

Hi guys! Thanks a lot both of you, because you've answer so quickly to my message. I'm a newbie on Excel, I'm trying to learn everyday. I will try your suggestions to see if it works as it should be.

In the future surely I will have more questions for you. Have a blessed day!:)

Yep, It works like a charm. Tks so much!:kiss:

I marked this thread as "solved" for you..