Two different formulas for two different conditions- Urgency High

srinath tantri

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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try:
Change ranges to match your data.
Excel Workbook
ABC
1Receipt amountClaim $ Cond. 1Claim $ 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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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