Hi,
Ref to enclosed attachment, I need help in writing formula to achieve below results please
IF Amount is between 5,00,000 AND 7,50,000 THEN (AMOUNT * 10%) /12
IF Amount is between 7,50,001 AND 10,00,000 THEN (AMOUNT * 15%) /12
IF Amount is between 10,00,000 AND 12,50,000 THEN (AMOUNT * 20%) /12
IF Amount is between 12,50,001 AND 15,00,000 THEN (AMOUNT * 25%) /12
IF Amount is greater than 15,00,001 THEN (AMOUNT * 30%) /12
IF all conditions are false then 0
you could use a table so you can change the range
But a nested IF
=IF( A2 >1500000, (A2*0.3)/12, IF( A2 >1250000, (A2*0.25)/12,IF( A2 >1000000, (A2*0.2)/12,IF( A2 >750000, (A2*0.15)/12,IF( A2 >500000, (A2*0.1)/12,0)))))
What happens between 0 and 500000
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
How about
=A2*LOOKUP(A2,{0,500001,750001,1000001,1250001,1500001},{0,0.1,0.15,0.2,0.25,0.3})/12
What happens between 0 and 500000
It will be zero
This =A2*LOOKUP(A2,{0,500001,750001,1000001,1250001,1500001},{0,0.1,0.15,0.2,0.25,0.3})/12 looks simpler. Can you please explain this formula
It looks up the value of A2 in the 1st array {0,500001,750001,1000001,1250001,1500001} (if no exact match it finds the nearest lower number) and returns the relevant value from the 2nd array.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks