If A1 is up to 1,00,000, B1 will 500; If A1 is between 1,00,001 to 2,00,000, B1 will 1000; If A1 is greater than 2,00,000, for every 1,00,000 or fraction two hundred extra will be added with 1000 in B1 cell e.g. for 3,00,000 B1 will 1000+200, for 3,05,000 B1 will 1000+200+200 etc.
What'll be the nested function of above calculation.
Plz help.
Thanks in advance
Last edited by nihar sharma; 02-17-2017 at 12:20 PM.
=IF(A1>=1000000,500,IF(A1<=2000000,1000,IF(A1>2000000,1000+(3000000-A1)/1000000*200)))
It's a bit hard to understand what you mean by "for every 1,00,000 or fraction two hundred extra will be added with 1000 in B1 cell e.g. for 3,00,000 B1 will 1000+200, for 3,05,000 B1 will 1000+200+200 etc." so I did the above based on what I could understand. I assume "1,00,000" in your post = 1 million = 1,000,000
You either quit or become really good at it. There are no other choices.
Here's another way, which doesn't use nested IFs:
=MIN(2,INT((A1+999999)/1000000))*500 + (A1>2000000)*ROUNDUP((A1-2000000)/1000000,0)*200
Put this in B1, then copy down.
Hope this helps.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks