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 units greater than 10 to 20 units and an additional $100 bonus on all units > 20.

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

Excel formula | Microsoft Community Hub

15 Replies

PReagan
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))
- excelwnb
  Copper Contributor
  Jan 05, 2020
  PReagan duh figured it out sorry.
- excelwnb
  Copper Contributor
  Jan 01, 2020
  PReagan I am using this formula =if(Summary2020!$O$14>137700,2,8), however, I am realizing that once the amount reaches $137,700 then all cells will revert to 2 rather than stay as an 8 if it's less than $137,700. Do you know how to tell it to keep the cell at 8?
  - PReagan
    Bronze Contributor
    Jan 02, 2020
    excelwnb
    
    The formula that you shared works as follows:
    If "Summary2020!$O$14" is greater than 137700, then return 2.
    If "Summary2020!$O$14" is less than or equal to 137700, then return 8.
    You say that "once the amount reaches $137,700 then all cells will revert to 2 rather than stay as an 8...". Based on the formula, if "Summary2020!$O$14"=137700, then 8 will be returned. If you would like the formula to return 2 for all values greater than or equal to 137700, then change your formula to =if(Summary2020!$O$14>=137700,2,8).
    
    Edit: If "Summary2020!$O$14"=137700 and is returning 2, then this could be caused by hidden rounding. For example, cell "Summary2020!$O$14" may show 137700 but actually be 137700.10. To check this, expand the width of the cell and use the increase decimal button (located in the Number section of the Home tab).
- 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.

Forum Discussion

Excel formula

15 Replies

Resources