EXCEL Multiple Ranges - need different answers for

问题:

I have spent a few hours working out how to do this which is why im posting it here now... If you want to return different values in a cell based on which range the value entered in another cell comes under then I have worked out how to do it!! (bear in mind that this is specific to my spreadsheet and was for calculating prices i.e. 0.99 = £0.99)

For example:

1. IF G2 is ABOVE "0" BUT BELOW "1" THEN display "0.1"
2. IF G2 is ABOVE "0.99" BUT BELOW "5" THEN display "0.15"
3. IF G2 is ABOVE "4.99" BUT BELOW "15" THEN display "0.2"
4. IF G2 is ABOVE "14.99" BUT BELOW "30" THEN display "0.5"
5. IF G2 is ABOVE "29.99" BUT BELOW "100" THEN display "1.0"
6. IF G2 is ABOVE "99.99" THEN display "1.30"

So IF G2 was "£18.75" then the cell that this formula is entered in would display "£0.50" based on the value's above.

You will see the above formula contained within the answer to this question below......

回答1:

use

=VLOOKUP(D4,F4:G9,2)

with the range F4:G9:

0   0.1
1   0.15
5   0.2
15  0.3
30  1
100 1.3

and D4 being the value in question, e.g. 18.75 -> result: 0.3

回答2:

Nested if's in Excel Are ugly:

=If(G2 < 1, .1, IF(G2 < 5,.15,if(G2 < 15,.2,if(G2 < 30,.5,if(G2 < 100,.1,1.3)))))

That should cover it.

回答3:

So... below is the formula I would have used but The above answers look a lot neater!...

=IF(AND(G2>0,G2<1),0.1,IF(AND(G2>0.99,G2<5),0.15,IF(AND(G2>4.99,G2<15),0.2,IF(AND(G2>14.99,G2<30),0.5,IF(AND(G2>29.99,G2<100),1,IF(G2>99.99,1.3))))))

and looking at it split apart looks like this...

=IF(AND(G2>0,G2<1),0.1,
 IF(AND(G2>0.99,G2<5),0.15,
  IF(AND(G2>4.99,G2<15),0.2,
   IF(AND(G2>14.99,G2<30),0.5,
    IF(AND(G2>29.99,G2<100),1,
     IF(G2>99.99,1.3)
    )
   )
  )
 )
)

Thank you for your answers guys!! At least people will have a number of things to choose from now.. :-)