Excel formula

[excelwnb]

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.

[#2 · Accepted solution]

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

[#3]

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

[#4]

@excelwnb 

Perhaps

=MIN(1595,if(C15="yes",C20*30%,if(C16="yes",C20*20%,0)) )

[#5]

@Sergei Baklan 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%

[#6]

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.

[#7]

Wow @PReagan you are awesome. Yes it looks like that is working. thank you very much. 

[#8]

@excelwnb 

Replace MIN with MAX, then it will work.

[#9]

You’re welcome!

[#10]

@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?

[#11]

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

[#12]

@PReagan I think where it is getting caught up is the let's say cells A18 through ZZ18 are all calculating income, using the formula =if(Summary2020!$O$14>=137700,2,8), A18, B18, C18, etc...will all calculate "8" based on the formula as the total income has not yet reached $137,700, but when the income does reach $137,700 on AA - ZZ as an example, then AA - ZZ calculate correctly as "2", but A-Z also then revert back to "2" when they originally calculated "8" and should remain "8" since the total income on A-Z was < $137,700. So I want the calculation to change to 2 only on the Cells where the income was > $137,700 when it was calculated. I think I may need to not reference the summary screen and use the cells as they are calculating to accomplish this but thought I would check to see if there was an easier way. 

[#13]

@excelwnb 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

[#14]

@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

[#15]

@PReagan duh figured it out sorry. 

[#16]

@excelwnb

I apologize, I have not checked the forums in a few days. For future reference, please post new questions as a new thread instead of as a reply to an old thread.

Thank you!