Forum Discussion

Copper Contributor

Dec 13, 2019

Solved

Excel formula

multiple IF or possible and/or functions. Trying to calculate if C8 is 0,0, if C8 is between 11-20 then C8*50, if C8 is > 20 then C8*100 plus the 500 from C8*10. end result is a $50 bonus per file on...

excel

Formulas and Functions

PReagan
Dec 13, 2019
Hello excelwnb,

Assuming there is no bonus between 0 and 10 units:
=IF(C8>20,(C8-20)*100+500, IF(C8>=11,(C8-10)*50,0))

excelwnb

Copper Contributor

Dec 14, 2019

PReagan this worked perfectly thank you.

Can I ask if you have insight into adding a minimum $1,595 to this formula? =if(C15="yes",C20*30%,if(C16="yes",C20*20%,0)). the question being if C20*30 is not at least $1,595 then the min calculation would be $1,595.

PReagan

Bronze Contributor

Dec 14, 2019

My pleasure!

Assuming a minimum of 1595 would be applied to only C20*30% and not C20*20% then that could be:
=IF(C15=“yes”, IF(C20*30%<1595, 1595, C20*30%), IF(C16=“yes”, C20*20%, 0))

This formula was created on my phone so I am not able to test it. Please let me know if it works for you.

excelwnb
Copper Contributor
Dec 14, 2019
Wow PReagan you are awesome. Yes it looks like that is working. thank you very much.
- PReagan
  Bronze Contributor
  Dec 14, 2019
  You’re welcome!
  - excelwnb
    Copper Contributor
    Jan 05, 2020
    PReagan here is my updated formula, any idea why it is not working? =if(O5=0,0,if(O2+sum(M2:N2)<13,1295,995)). this is the 13th cell so it should be returning 995 but it is still returning 1295