Forum Discussion
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))
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?
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).
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.
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.
- 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. 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%
