12-13-2019 05:03 AM
12-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.
12-14-2019 04:49 AM
01-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?
01-02-2020 12:50 PM - edited 01-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).
01-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.