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...

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))

Bronze Contributor

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))

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!
SergeiBaklan
Diamond Contributor
Dec 14, 2019
excelwnb

Perhaps

=MIN(1595,if(C15="yes",C20*30%,if(C16="yes",C20*20%,0)) )
- excelwnb
  Copper Contributor
  Dec 14, 2019
  SergeiBaklan thank you for responding. Unfortunately, that did not work, it still calcuated C20*30%. =MIN(1595,if(C15="yes",C20*30%,if(C16="yes",C20*20%,0))). In addition to clarify I only want C20*30 to be min 1595, not the C20*20%
  - Riny_van_Eekelen
    Platinum Contributor
    Dec 14, 2019
    excelwnb
    Replace MIN with MAX, then it will work.

Resources