Dec 13 2019 05:03 AM
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.
Dec 13 2019 07:05 AM
SolutionHello @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))
Dec 14 2019 04:16 AM
@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.
Dec 14 2019 04:24 AM
Dec 14 2019 04:43 AM
@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%
Dec 14 2019 04:49 AM
Dec 14 2019 05:04 AM
Wow @PReagan you are awesome. Yes it looks like that is working. thank you very much.
Jan 01 2020 01:02 PM
@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?
Jan 02 2020 12:50 PM - edited Jan 02 2020 01:22 PM
The formula that you shared works as follows:
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).
Jan 03 2020 07:24 AM
@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.
Jan 05 2020 04:37 AM
@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
Jan 05 2020 04:41 AM
@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
Jan 06 2020 06:10 AM
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!
Dec 13 2019 07:05 AM
SolutionHello @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))