Excel Formula

[wozz]

Been years since i did this sort of thing, still cant believe i know nothing and I done a excell project as a part of a higher GNVQ!

Anyway, not even sure if this is possible.

I want a cell to look at another cells result then compare it to see if its between two figures if it is gives a result.  for example:

Cell result = 4

Next cell looks at it and sees if its between 0-5.99 it is, so the result is 6.

next block of numbers would be 6-9.99 result would be 10.

I think you will be able to see where this is going all the way up to 63.  is that possible?

 

[misssweden]

I can't quite get my brain into gear tonight but I think you would be able to do that with an IF statement.

Don't ask me how though

 

[wozz]

ive been playing, Yes i can do it with a if statement but only for one set on numbers, i dont seem to be able to make it look at numerous sets of number to get a result.  

 

[Barx]

you need to nest the IF's. the next IF becomes the Else / False of the previous. Note, And use logic operators to get the ranges.

if you comfortable with VBA or at least willing to give it a go. This type of operation is better suited to a Select Case statement.

 

[wozz]

Thanks Barx, that lot flew over my head though, most in depth i got into this before was Vlookup and macros.  

So you cant do multiple IFs or multiple ORs? 

 

[Barx]

yes you can, post what you have so far, plus some more of the ranges you want and i'll try to give an example.

I think your limited to 7 nested if statements in excel, so you may be stuffed!

well in that case...

 

[wozz]

I can live with 7 above 50 is a rare one any way.

Ok barx.

0-5.99=6

6-9.99=10

10-15.99=16

16-19.99=20

20-31.99=32

32-39.99=40

40-49.99=50

50-62.99=63

>63=invalid

Is that possible?

 

[Barx]

try this pal

=IF(AND($B$2 > 0, $B$2 < 5.99), 6, IF(AND($B2 >= 6, $B$2 < 9.99), 10, IF(AND($B$2 >= 10, $B$2 < 15.99), 16, IF(AND($B$2 >= 16, $B$2 < 19.99), 20, IF(AND($B$2 >= 20, $B$2 < 31.99), 32, IF(AND($B$2 >= 32, $B$2 < 39.99), 40, IF(AND($B$2 >= 40, $B$2 < 49.99), 50, IF(AND($B$2 >=50, $B$2 < 62.99), 63, ">63 invalid"))))))))
I put if the A = a mcb value it jumps up to next, i.e 16A will need 20A mcb. Easily editable if you want it to behave differently.

Also this assumes the value to be tested will be in cell B2

 

[wozz]

Thankyou will give it a go later, and let you know how i get on. 

 

[wozz]

Absolutely spot on mate! :^O   

 

[MeFil]

A slightly neater way to do it could be like this. Assume your result is in cell b2 then you could use the following

=if (b2> 63;"invalid"; if(b2> 50; 63; if(b2> 40; 50;if(b2> 32; 40; if(b2> 16; 32;if(b2> 10; 16; if(b2> 6; 10; if(b2> 0; 6; 0)))))))) 
Think that works too!

 

[Barx]

A slightly neater way to do it could be like this. Assume your result is in cell b2 then you could use the following

=if (b2> 63;"invalid"; if(b2> 50; 63; if(b2> 40; 50;if(b2> 32; 40; if(b2> 16; 32;if(b2> 10; 16; if(b2> 6; 10; if(b2> 0; 6; 0)))))))) 

Think that works too!

I had thought about something like that no sooner had I posted above, but didn't have time to re-do it.

 

[septiclecky]

Try this link for excel as it gives worked examples

http://www.hoffits.com/xlfdic/

 

[wozz]

Thanks lads,

 

can some one explain the above to me (me fils one, i cant quote it not allowed), barx's one sort of made sense how it worked, but the above doesnt for some reason.

cheers

 

[MeFil]

The if statement test in a sequential order. As in "if this, then do that, else do the other" so my version looks for the numbers above 63 first and reports "illeagle" if it finds one. Next it looks to see if its above 50, then 40, then 32 and so on. It stops testing when it finds a true answer.

Eg. Suppose the cell value b2 is 49. The if statment will test if b2 is above 63 first, which it isn't. So then it will test if it is above 50, which it isn't. Next it will test if it is above 40, which it is! It will now output "50" as the answer and stop testing any further.

This way you only have to test the cell to see if the number is ABOVE a certain value and not BETWEEN certain values. With me so far??? I'm not sure I've helped with my explanation.

The main benefit of doing it this way is that its a lot less typing and is a shorter formula to manage. But both approaches work.

Fil

I had thought about something like that no sooner had I posted above, but didn't have time to re-do it.

That's the fun of excel..lots of different ways to do things.

Showed my daughter how to use it a while back. Had a look at some of the stuff she does with it now for school and was completely stumped by how she was making it do things. Makes my efforts look really amateur.

 

[wozz]

explained perfectly thanks

 

[Sidewinder]

OK,if this is for MCB ratings why have you missed out the 25A?...

Oh & the 1, 2, 3, & 4A versions as well.

Or, I could have missed the point completely!...

 

[steptoe]

what about 5s and 30s too?

 

[wozz]

Sorry lads just the current ones in 7671.  

This spreadsheet is big enough as it is.  

 

[Sidewinder]

5's, 13's & 30's are not 60898 values, the ones I listed are!...

I believe that 25 is listed in 7671...