Two different formulas for two different conditions- Urgency High

[srinath tantri]

Hello Guys,

Hope you are doing well today,

Can you please assist me in providing the two different functions for each of the below two scenarios

Formula required for the below condition for first excel sheet:

If B7 = Receipt Amount
E7 = Claim Amount

· Claim amount = If Receipt amount<=35000 then the value in Claim Amount = 100% of receipt amount ----- Condition 1

· Claim Amount = If receipt amount>35000 then

Up to 35000 the value in claim amount = 100% + Receipt amount > 35000 = 70% and maximum claim amount can be $90000 ----------Condition 2

Ex: Receipt amount= $ 120000
Claim amount = up to $35000=100%
<wbr> For remaining $85000 = 70%
Claim amount = 35000(100%) + 85000(70%)
= 35000+59500
= 94500
As the claim amount is more than 90000 I want Claim amount to showcase maximum of $90000

Ex 2: Receipt amount= $90000
Claim amount = up to $35000= 100%
<wbr> For remaining 55000= 70%
Claim amount = 35000+55000(70%)
<wbr>= 35000+38500
= 73500

Formula required for the below condition which needs to be placed in a separate excel sheet:

If B7 = Receipt Amount
E7 = Claim Amount

· Claim amount = If Receipt amount<=25000 then the value in Claim Amount = 100% of receipt amount ----- Condition 1

· Claim Amount = If receipt amount>25000 then

Up to 25000 the value in claim amount = 100% + Receipt amount > 25000 = 70% ----------Condition 2

The condition is similar to the above provided however this does not have any maximum limit and the amount is different.

 

[AhoyNC]

Try:
Change ranges to match your data.

Excel Workbook
	A	B	C
1	Receipt amount	Claim $ Cond. 1	Claim $ Cond. 2
2	$ 120,000.00	$ 90,000.00	$ 91,500.00
3	$ 90,000.00	$ 73,500.00	$ 70,500.00
4	$ 15,600.00	$ 15,600.00	$ 15,600.00
5	$ 27,000.00	$ 27,000.00	$ 26,400.00
Sheet

 

[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.