Auto calculation Function Required- High Urgency

[srinath tantri]

Hello Guys,

Hope you are doing well today,

I appreciate your quick response and help on the above concept, however i have few things which i want to bring to your notice and require your assistance in getting one single function for the previous scenario provided in addition to the other two scenarios.

The old function would remain the same as you have provided i.e. for the below notes:

There is a small correction to the concept I have posted earlier, I want the functions to work on additional validations and will require our help:

Assume the below values:

B10 = Receipt Amount
E10 = Previous Claim amount
G10 = Current Claim amount

Where I have two scenarios to be fulfilled:

Scenario 1:

· If Receipt amount = 55000
· Previous claim amount = 35000

So looking at the above condition we know that 90000 is the maximum Current claim amount (G10) where 100% will be provided up to 35000 and 70% for remaining 55000.

As 35000 has already been claimed at 100% previously, I want the function which should calculate the receipt amount 55000 on 70% when previous claim amount is more than or equal to 35000.

I.e. Current Claim amount (G10) = If E10 >= 35000 then 70% of B10 ------------------------------<wbr>---Condition1 (The function provided to this must be addition to the function provided
Previously i.e. (=MIN (IF (A2<=35000, A2, (A-35000)*0.7+35000), 90000)


Scenario 2:

· If receipt amount = 50000
· Previous claim amount = 15000

From the above details we can understand that 15000 have been claimed previously at 100%. So if the receipt amount is provided as 50000 then I want the function which would calculate as below

I.e. Current Claim amount =

If previous claim amount is less than 35000,
Then previous claim amount (15000) - 35000 = 20000 (100%)
And 20000- 50000(Receipt amount) = 30000(70%) (Remaining amount).

= 20000(100%) +30000(70%) = 41000

I.e. Current claim amount (G10) = If E10 < 35000, then E10-35000 = “X” Value and “X”-B10 = “Y” value.
Current claim amount (G10) = “X value” (100%) + “Y value” (70%). ------------------------------<wbr>-------- Condition2 (The function provided to this must be addition to the function provided previously + condition1 function).

Final function required should suffice- Previous function provided+ Function for condition 1+ Function for condition 2

I hope i have not confused you with too much of information above, I hope you are able to provide the best resolution for all the conditions and appreciate all your help.

Thank you so much!

Hope you have a nice weekend.

 

[Fluff]

This is a continuation, of this https://www.mrexcel.com/forum/excel...rent-conditions-urgency-high.html#post4984534

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

You had a response to your original post, which you have apparently ignored (or at least not responded to).
As we are all volunteers, it is a matter of common courtesy to acknowledge any help given & provide some sort of feedback. That way you are more likely to get help again in the future.