Condtional formulae

[srinath tantri]

Hello all,

I have a project to work on where i am unaware and failed to get output i am looking for. Can anyone look into the below description with condtions and help me with a code which would fulfil this.

For example:

B7 = Receipt amount
E7 = Claim amount.

Claim amount= If Receipt amount<=35000 then the value in Claim amount=100% of Receipt amount ---(Condtion 1)

= If Receipt amount>35000 & Receipt amount<=90000
until 35000 the value in Claim amount=100% + Receipt amount>35000 & Receipt amount<=90000 =70% ----- (Condtion 2)

Ex : Receipt amount= 89000
Claim amount = 35000=100%
= 54000=70%
= 100%+70%
Claim amount = 35000(100%)+ 37800(70%)
= 72800


= If Receipt amount>90000 until 35000 the value in Claim amount=100% + Receipt amount>35000 & Receipt amount<=90000 =70%. ---(Conditon 3)
Receipt amount exceeding 90000=0%

Ex : Receipt amount = 95000
Claim amount = 35000 = 100%
= 55000 = 70%
= 5000 = 0%
= 100%+ 70% + 0%
Claim amount = 35000(100%)+ 38500 (70%) + 0
Claim amount = 73500

I woulld really appreciate in advance for helping me out in resolving this.

Thank you so much,

Hope you all have a great rest of the day!

 

[Legacy 143009]

Have a nice day

=IF(B7<=35000,B7,IF(AND(B7>35000,B7<90000),0.7*(B7+15000),IF(B7>=90000,7350)))

 

[wideboydixon]

I think the last number in the formula above should be 73500. However, an alternative to consider:

Book1
	B	C	D	E
6	Receipt Amount			Claim amount
7	20000			20000
8	89000			72800
9	95000			73500
Sheet1

Cell Formulas
Range	Formula
E7	=MIN($B7,35000)+MAX(0,MIN($B7-35000,55000))*0.7

WBD

 

[Legacy 143009]

I think the last number in the formula above should be 73500. However, an alternative to consider:

Book1
	B	C	D	E
6	Receipt Amount			Claim amount
7	20000			20000
8	89000			72800
9	95000			73500
Sheet1

Cell Formulas
Range	Formula
E7	=MIN($B7,35000)+MAX(0,MIN($B7-35000,55000))*0.7

WBD

Oh yes, it is! Thanks BTW, you formula looks nice and clean

Let me write down again:
=IF(B7<=35000,B7,IF(AND(B7>35000,B7<90000),0.7*(B7+15000),73500))

 

[wideboydixon]

Took me a while to figure out that 70% of 50000 = 35000

FWIW, you can drop the AND() in the second part since if B7 is not <=35000, it follows that it must be >35000:

=IF(B7<=35000,B7,IF(B7<=90000,0.7*(B7+15000),73500))

WBD

 

[Legacy 143009]

Took me a while to figure out that 70% of 50000 = 35000

FWIW, you can drop the AND() in the second part since if B7 is not <=35000, it follows that it must be >35000:

=IF(B7<=35000,B7,IF(B7<=90000,0.7*(B7+15000),73500))

WBD

Ahh you are right!