Auto calculation Function Required- High Urgency

Status
Not open for further replies.

srinath tantri

New Member
Joined
Dec 10, 2017
Messages
12
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:

0


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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top