srinath tantri
New Member
- Joined
- Dec 10, 2017
- Messages
- 12
Hello all,
Hope you are doing well,
Can anyone assist me in correcting the formula which lacks a function/condition to suffice my project.
Assume the below cell values information provided:
· B9 = Receipt amount
· I9 = Level of Graduation
· M9 = Grade Achieved
· Q9 = Claim amount
Claim amount (Q9) = 100% of receipt amount (B9) if Grade achieved (M9) =”A” and if Level of Graduation (I9) = “Under Graduated” maximum up to 11500 and
= “Post graduated” maximum up to 18500.
<tbody>
</tbody>
Claim amount (Q9) = 75% of receipt amount (B9) if Grade achieved (M9) = “B” and level of graduation (I9) = “Under Graduated”
= “Post graduated”
If receipt amount (B9) exceeds 11500 for Under Graduate (I9) then 75% on 11500
If receipt amount (B9) exceeds 18500 for Post Graduate (I9) then 75% on 18500
<tbody>
</tbody>
Claim amount (Q9) = 0% of receipt amount (B9) if Grade achieved (M9) = “C”
<tbody>
</tbody>
Formula already available is =MIN(B9*IF(M9="A",100%,IF(M9="B",75%,0%)),IF(UPPER(LEFT(I9,1))="P",18500,IF(UPPER(LEFT(I9,1))="U",11500)))
This formula calculates 75% on what ever receipt amount is provided.
However we need to have formula corrected i.e. when receipt amount exceeds 11500 for under graduate and 18500 formula must calculate 75% on the MAX amount.
Thank you all and appreciate all your efforts in providing the resolution on this promptly in advance!
Have a great day ahead!
Hope you are doing well,
Can anyone assist me in correcting the formula which lacks a function/condition to suffice my project.
Assume the below cell values information provided:
· B9 = Receipt amount
· I9 = Level of Graduation
· M9 = Grade Achieved
· Q9 = Claim amount
Claim amount (Q9) = 100% of receipt amount (B9) if Grade achieved (M9) =”A” and if Level of Graduation (I9) = “Under Graduated” maximum up to 11500 and
= “Post graduated” maximum up to 18500.
Q9 = B9 (100%) if M9=”A” and if I9= “Under Graduated” then maximum amount = 11500 and I9= “Post Graduated” then maximum amount=18500. ------------Condition 1 |
<tbody>
</tbody>
Claim amount (Q9) = 75% of receipt amount (B9) if Grade achieved (M9) = “B” and level of graduation (I9) = “Under Graduated”
= “Post graduated”
If receipt amount (B9) exceeds 11500 for Under Graduate (I9) then 75% on 11500
If receipt amount (B9) exceeds 18500 for Post Graduate (I9) then 75% on 18500
(Q9) = B9 (75%) if M9= “B” and if I9= “Under Graduated” I9= “Post Graduated” -----------Condition 2 If B9> 11500 for “Under Graduated” then 75% on 11500 for Under Graduate. If B9>18500 for “Post graduated” then 75% on 18500 for Post graduate. |
<tbody>
</tbody>
Claim amount (Q9) = 0% of receipt amount (B9) if Grade achieved (M9) = “C”
(Q9) = B9 (0%) if M9=“C” -------------Condition 3 |
<tbody>
</tbody>
Formula already available is =MIN(B9*IF(M9="A",100%,IF(M9="B",75%,0%)),IF(UPPER(LEFT(I9,1))="P",18500,IF(UPPER(LEFT(I9,1))="U",11500)))
This formula calculates 75% on what ever receipt amount is provided.
However we need to have formula corrected i.e. when receipt amount exceeds 11500 for under graduate and 18500 formula must calculate 75% on the MAX amount.
Thank you all and appreciate all your efforts in providing the resolution on this promptly in advance!
Have a great day ahead!