Floor Function Not Working

[VinceF]

Greetings,

This should be an easy one for the experts.

In cell G12 the result of the formula is 54, I tried every conceivable way that I can think of to configure the formula so that the Floor function rounds the 54 to 50

Appreciate you for your assistance.

VinceF
Excel 2016

=IF(K12=1,PAY!$C$27,IF(K12=2,PAY!$D$27,IF(K12=3,PAY!$E$27,FLOOR(G12,5))))

 

[RoryA]

That should work. What value are you getting?

 

[etaf]

i'm assuming K12 does not = 1,2 or 3
seems to work on its own, nearest 5

 

[VinceF]

""i'm assuming K12 does not = 1,2 or 3""
Incorrect....K12=2 in the formula which is 54

""seems to work on its own, nearest 5""
Correct, if it stands alone it works just fine, however it is not working in the formula as written. I tried several different configurations and at this point I'm not able to get it working.

=IF(K12=1,PAY!$C$27,IF(K12=2,PAY!$D$27,IF(K12=3,PAY!$E$27,FLOOR(G12,5))))

This formula resides in cell G12. the result of the formula is 54, trying to use the Floor function to round it down to the nearest 5 which would be 50

 

[etaf]

K12=2 in the formula which is 54

OK, so if K12 =2

Then it will NOT do the floor part

=IF(K12=1,PAY!$C$27,IF(K12=2,PAY!$D$27,IF(K12=3,PAY!$E$27,FLOOR(G12,5))))

IF(K12=2,PAY!$D$27 - then this is the bit thats calculated
so returns the value from PAY!$D$27

G12 is not used as K12 has to be FALSE and so NOT = 1,2, or 3

 

[VinceF]

If I understand you correctly... then yes.
IF(K12=2,PAY!$D$27 - then this is the bit thats calculated

The result of IF(K12=2 is true so it looks a sheet named "pay", specifically cell D27 which has the number "54" in that cell.
That value then is inserted in cell G12

 

[etaf]

ok, so thats just a value that goes into G12
the floor() is not used - hence you get 54

Maybe you need floor on all those TRUE values , if thats what you want
IF(K12=2,PAY!$D$27
to
IF(K12=2,FLOOR(PAY!$D$27,5)

 

[VinceF]

etaf,

That worked....!!!!
Thank you very much....this is what the formula looks like now.

=IF(K12=1,FLOOR(PAY!$C$27,5),IF(K12=2,FLOOR(PAY!$D$27,5),IF(K12=3,FLOOR(PAY!$E$27,5))))

You guys are the best...!!!

VinceF

 

[etaf]

you are welcome
HOWEVER,

you may want a false - if K12 does not = 1,2 or 3
then non are true and so a FALSE will be returned into the cell
is that what you want for a FALSE condition ?????

=IF(K12=1,FLOOR(PAY!$C$27,5),IF(K12=2,FLOOR(PAY!$D$27,5),IF(K12=3,FLOOR(PAY!$E$27,5), "what to put here ?, 0 or "" ")))

 

[VinceF]

a "FALSE" error message is ok as I have a conditional format in the cell(s) that changes the font color to white.

Thanks again