In Excel Sheet, I want to get results based on slab e.g.:
if amount is between 0 to 2000 the cell should display 0, and
if amount is between 2001 to 2500 the cell should display 30, and if amount is between 2501 to 3500 the cell should display 60, and if amount is between 3501 to 5000 the cell should display 120,and if amount is between 5001 to 10000 the cell should display 175 or else it should display 200.
"THE RESULTANT FIGURE SHOULD BE NUMERIC VALUE"
Help me.
2006-10-13 01:40:59 · 8 answers · asked by hgaunr 1 in Computers & Internet ➔ Software
=IF(A1<2001, 0, IF(A1<2501, 30, IF(A1<3501, 60, IF(A1<5001, 120, IF(A1<10001, 175, 200)))))
2006-10-13 01:52:56 · answer #1 · answered by O Caçador 6 · 1⤊ 1⤋
Dougneb must be a bonehead because all the answers above his work. His has a problem though (of course he's not alone; the AND function is a waste of time on this type of IF statement, it's not needed).
My solution -- VLOOKUP
Setup an array
0>>>>>>>0
2001>>>>30
2501>>>>60
3501>>>>120
5001>>>>175
10001>>>200
Enter the formula next to the amount (change A1 to the actual cell where the amount is)
=VLOOKUP(A1,array,2)
2006-10-13 15:32:30 · answer #2 · answered by K-Man 2 · 1⤊ 1⤋
Sounds like a complicated, nested set of if statements will work.
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=242
This site has some info about it. I would nest if/and statements together, like if((and(A1>=0, A1<=2000)),0,if((and(A1>=2001,A1<=2500)),30,if.....) and so on like that until you're out of conditions.
There may be a better way to do it... this came first to my mind.
2006-10-13 01:53:12 · answer #3 · answered by agentdenim 3 · 0⤊ 0⤋
Assume A2 is the cell your inspecting against the criteria.
=IF(AND(A2<=2000,A2>=0),0,
IF(AND(A2<=2500,A2>=2001)
,30,IF(AND(A2<=2501,A2>=3500)
,60,IF(AND(A2<=3501,A2>=5000)
,120,IF(AND(A2<=5001,A2>=10000)
,175,200)))))
Just make these lines a single string
2006-10-13 02:03:16 · answer #4 · answered by kyrie_eleison_gr 5 · 0⤊ 0⤋
The formula could desire to be like this... =IF(AND(A1 >= 0, A1<= 2000),"0", IF(AND(A1 > 2000, A1<= 2500),"30",IF(AND(A1 > 2500, A1<= 3500),"60",IF(AND(A1 > 3500, A1<= 5000),"a hundred and twenty",IF(AND(A1 > 5000, A1<= ten thousand),"one hundred seventy five","2 hundred"))))) the place A1 - the cellular whose fee is to be analysed.... try Case result : selection - result 10 - 0 500- 0 5010 - one hundred seventy five 6500 - one hundred seventy five 4500 - a hundred and twenty 3200 - 60 2700 - 60 7000 - one hundred seventy five 10001 - 2 hundred 2010 - 30 3700 - a hundred and twenty i'm hoping this could remedy your subject... satisfied Computing... Hare Krsna
2016-10-16 03:49:36 · answer #5 · answered by Anonymous · 0⤊ 0⤋
ok, how about this:assuming cell A1 has the value to test:
=if(A1<=2000,0,
if(A1<=2500,30,if(A1<=3500,60,
if(A1<=5000,120,if(A1<=10000,
175,200)))))
The above is all in one line.
You did not mention what would happen if A1<0 so the above assumes that A1 is non-negative.
pretty cool when you relly figure it out...
-jose-
2006-10-13 01:54:22 · answer #6 · answered by ? 2 · 0⤊ 1⤋
None of the others work. This does:
=IF(AND(A1>=0,A1<=2000),1,
IF(AND(A1>=2001,A1<=2500),30,
IF(AND(A1>=2501,A1<=3500),60,
IF(AND(A1>=3501,A1<=5000),120,
IF(AND(A1>=5001,A1<=10000),175
,200)))))
All on one line
2006-10-13 02:01:04 · answer #7 · answered by dougneb 3 · 0⤊ 2⤋
yes u can do this by implementing if condition on cell
like
=if(a2>5001,"175",if(a2>3501,"120....
so on and u will get result
OK bye for now
2006-10-13 02:32:23 · answer #8 · answered by Anonymous · 0⤊ 1⤋