Conditional Coding/Formula functions-Correction( High Urgency)

[srinath tantri]

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.

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!

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel...al-coding-formula-functions-high-urgency.html

Please do not post the same question multiple times (especially since you already got a reply in the other thread that you have not responded to!).
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).