 • 590K Members
• 4,602 Online
• 716K Conversations
SOLVED

Highlighted

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

15 Replies
Highlighted
Solution

# Re: Excel formula

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

Highlighted

# Re: Excel formula

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

Highlighted

# Re: Excel formula

Perhaps

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

Highlighted

# Re: Excel formula

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

Highlighted

# Re: Excel formula

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

# Re: Excel formula

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

Highlighted

# Re: Excel formula

Replace MIN with MAX, then it will work.

Highlighted

You’re welcome!
Highlighted

# Re: Excel formula

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

Highlighted

# Re: Excel formula

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

Highlighted

# Re: Excel formula

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

Highlighted

# Re: Excel formula

@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

Highlighted

# Re: Excel formula

@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

Highlighted

# Re: Excel formula

@PReagan duh figured it out sorry.

Highlighted

# Re: Excel formula

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

Related Conversations
SUMMING
dumby58 in Excel on
1 Replies
how to set 2E02 into escel
Daryl20 in Excel on
5 Replies
Trouble with formulas MIN/MAX within IF statements
BDB1973 in Excel on
6 Replies
Openen en opslaan WORD en EXCEL
Turke100 in Excel on
0 Replies
historical stock data
onassis78 in Excel on
0 Replies
Excel cell formatting
snojoe10 in Excel on
4 Replies