IF formula impacted by previously entered data

Thanks in advance to all the Excel gurus out there.

I have attached a Data Sample file to walk through what we are trying to do ... if you look at the 'Summary' tab you will see some editable data attributes in cells J2:L6. Essentially what we are saying is that for tier 1 up to 500 hours the revenue accrual should be 25%, after 500 hours up to 1,000 hours the revenue accrual should be 10% and any hours greater than 1,000 the revenue accrual should be 5%. The issue I am running into is that we are looking to perform this calculation by month (see columns C-G on 'Summary' tab) and the data inputs stack over time.

For Jul-22 this math is easy: for contract 5000832870 I have 193 hours in Jul-22 (see 'PIVOT' tab); that falls below 500 hours so all 193 hours are allocated at 25% of revenue (193 hours ('PIVOT' cell E5 x rate per hour 'PIVOT' cell F5 x 25%). In Aug-22 I need to add 216 hours to 193 hours from July and I need to formula to confirm I'm still below 500 hours for the 25% allocation (or adjust to the new allocation % as needed).

As you can see the formula grows complex very quickly especially because the RPH changes by month as well.

I believe I'm on the right track with a nested IF formula, but also looking to see if there's an easier approach I'm not considering. I really appreciate all opinions and viewpoints - also happy to answer any additional questions.

I tried to back in logically to what you did, but I am still struggling. I am attaching an additional sample file - I have cut out all the noise and left a single contract example. You can see that the hours breach tiers going from prior FY to current year July 2022. I have properly prepared the nested IF formula in cell AJ10 and that formula is calculating properly. I have manually calculated what July 2022 should be in cell AK8, however I am struggling with the logic of the formula needed in cell AK10. I appreciate any insight you (or anyone else) may have.

For context:
- columns B-Q contain hours by month for the fiscal year (FY) and total hours occurring prior to the beginning of the current FY (col C)
- columns S-AG contain the blended rate per hour (RPH) by month along with the blended rate for all project hours occurring prior to the current FY (col T)
- columns AI-AX are where the calculations need to happen - cumulative hours need to be considered in order to calculate what tiers the current month hours are falling in (in this example 66.20 hours of current month hours should be tier 1 and the remaining should be tier 2) and the revenue math on the hours need to be at the current month rate

cells AI1:AK5 contain the progressive tiering information - the first 500 hours of a project should be allocated at 25% of revenue (hours x net RPH), the next 500 hours should be allocated at 10% of revenue (hours x net RPH) and any hours over 1,000 should be allocated at 5% of revenue (hours x net RPH). The orange cells will be editable as needed so the formulas in col AI:AX should reference them accordingly

Please let me know if anything is unclear - if the sumproduct idea works and I'm just not seeing it, can you please better explain? I appreciate it.

Please note: the correct amount that should be in cell AK10 is $6,127.92 (the calculated amount in AK8 is incorrect in the sample 2 file). Thank you.

Maybe try this formula in AK10:

=MIN(D10,MAX($AJ$3-SUM($C10:C10),0))*U10*$AK$3+
MAX(MIN(D10,MAX(SUM($C10:D10)-$AJ$3,0),$AJ$4-$AJ$3,$AJ$4-SUM($C10:C10)),0)*U10*$AK$4+
MAX(MIN(D10,SUM($C10:D10)-$AJ$4),0)*U10*$AK$5

Please Login or Register  to view this content.

And then drag across.