Applying Number to Range.

[Abid Raza]

Please help to Create Formula for Below:

I have to put Marks for result obtain.

If Cell E12 is between 33 & 41 E15 should return 4
If Cell E12 is between 34 & 42 E15 should return 3

Please help.......................

 

[Spencer101]

Please help to Create Formula for Below:

I have to put Marks for result obtain.

If Cell E12 is between 33 & 41 E15 should return 4
If Cell E12 is between 34 & 42 E15 should return 3

Please help........................

Hi,

I'm not sure I fully understand what you're trying to do here.

There appears to be an overlap. If the value in cell E12 was 34 to 4
it could fall within either of the two conditions and therefore coul
result in either 4 or 3 in cell E15.

Which would it be

 

[Abid Raza]

Hi,

I'm not sure I fully understand what you're trying to do here.

There appears to be an overlap. If the value in cell E12 was 34 to 4
it could fall within either of the two conditions and therefore coul
result in either 4 or 3 in cell E15.

Which would it be?

If Cell E12 is between 33 & 41 E15 should return 4
If Cell E12 is between 34 & 42 E15 should return 3

O.K., Can we defined ranges as:
1. below 33 0 Point
2. 33-34 3 Point
3. 34-41 4 Point
4. 41-42 3 Point
5. Above 42 0 Point

Hope this makes easy to solve..........

 

[isabelle]

hi Abid Raza,

if cell E12 = 40, what the outcome will, 3 or 4?


--
isabelle



Le 2012-06-02 11:42, Abid Raza a écrit :

 

[isabelle]

hi Abid Raza,

if cell E12 = 34, outcome will 3 or 4?
if cell E12 = 41, outcome will 3 or 4?

--
isabelle



Le 2012-06-02 17:28, Abid Raza a écrit :

 

[Ron Rosenfeld]

If Cell E12 is between 33 & 41 E15 should return 4
If Cell E12 is between 34 & 42 E15 should return 3

O.K., Can we defined ranges as:
1. below 33 0 Point
2. 33-34 3 Point
3. 34-41 4 Point
4. 41-42 3 Point
5. Above 42 0 Point

Hope this makes easy to solve...........

It does not make it easy. Your problem is not possible to solve as stated because you continue to define overlapping ranges.

By that I mean your table shows two different outcomes given the same value in E12.

Your Line 2. shows that 34 --> 3 point
but your Line 3. shows 34 --> 4 point

Also

Your Line 3. shows that 41 --> 4 point
but your Line 4. shows 41 --> 3 point.

 

[Vacuum Sealed]

Abid

I'm not sure how to ties all 3 of these into one neat package, but you
could use helper cell hidden off-screen somewhere and have E15 point to
the cell the shows the sum of the following:


BETWEEN 33 & 34 "=IF(AND(E$12<35,E$12>32),3,0)"
BETWEEN 35 & 40 "=IF(AND(E$12<41,E$12>34),4,0)"
BETWEEN 41 & 42 "=IF(AND(E$12<43,E$12>40),3,0)"

Now lets assume your helper cells are Z1 to Z4

Z1 = "=IF(AND(E$12<35,E$12>32),3,0)"
Z2 = "=IF(AND(E$12<41,E$12>34),4,0)"
Z3 = "=IF(AND(E$12<43,E$12>40),3,0)"
Z4 = "=SUM(Z1:Z3)

E15 = "=Z4"

Don't forget to remove the quote wrapping from the formula's.

This works well if you want to go down the track of using the helper
cell scenario, else hang on until someone else comes up with another
shorter, more direct idea.

Cheers

HTH
Mick.

 

[Spencer101]

If Cell E12 is between 33 & 41 E15 should return 4
If Cell E12 is between 34 & 42 E15 should return 3

O.K., Can we defined ranges as:
1. below 33 0 Point
2. 33-34 3 Point
3. 34-41 4 Point
4. 41-42 3 Point
5. Above 42 0 Point

Hope this makes easy to solve...........

Hi, there is still some overlap (i.e. 34 could be 3 or 4 points, a
could 41) but I've made some assumptions (above 34 and below 41 would b
4 points) and come up with this nested IF approach to be pasted int
cell E15.

=IF(E12="","",IF(E12<33,0,IF(AND(E12>=33,E12<=34),3,IF(AND(E12>34,E12<41),4,IF(AND(E12>=41,E12<=42),3,IF(E12>42,0))))))

It may need some tinkering if my assumptions are incorrect, but it'
easy enough to do.

I was thinking this might be easier with a lookup table (the formul
would certainly be shorter and easier to read) but you don't specif
what the lower and upper ranges would be, or if there may be decima
points.

This approach, although a long formula, will take care of all of that.

Let me know if it works for you.

S

 

[Vacuum Sealed]

Abid

I'm not sure how to ties all 3 of these into one neat package, but you
could use helper cell hidden off-screen somewhere and have E15 point to
the cell the shows the sum of the following:


BETWEEN 33 & 34 "=IF(AND(E$12<35,E$12>32),3,0)"
BETWEEN 35 & 40 "=IF(AND(E$12<41,E$12>34),4,0)"
BETWEEN 41 & 42 "=IF(AND(E$12<43,E$12>40),3,0)"

Now lets assume your helper cells are Z1 to Z4

Z1 = "=IF(AND(E$12<35,E$12>32),3,0)"
Z2 = "=IF(AND(E$12<41,E$12>34),4,0)"
Z3 = "=IF(AND(E$12<43,E$12>40),3,0)"
Z4 = "=SUM(Z1:Z3)

E15 = "=Z4"

Don't forget to remove the quote wrapping from the formula's.

This works well if you want to go down the track of using the helper
cell scenario, else hang on until someone else comes up with another
shorter, more direct idea.

Cheers

HTH
Mick.

And after spending some time away to mull over it, here's the short version.

=IF(AND(E12<35,E12>32),3,IF(AND(E12<41,E12>34),4,IF(AND(E12<43,E12>40),3,0)))

HTH
Mick

 

[Claus Busch]

Hi Mick,

Am Sun, 03 Jun 2012 18:48:28 +1000 schrieb Vacuum Sealed:

=IF(AND(E12<35,E12>32),3,IF(AND(E12<41,E12>34),4,IF(AND(E12<43,E12>40),3,0)))

a little bit shorter ;-)
=IF(OR(E12<33,E12>42),0,IF(OR(E12={33;34;41;42}),3,4))


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Sun, 3 Jun 2012 11:04:17 +0200 schrieb Claus Busch:

=IF(OR(E12<33,E12>42),0,IF(OR(E12={33;34;41;42}),3,4))

for your language version, I have to change semicolon to comma:
=IF(OR(E12<33,E12>42),0,IF(OR(E12={33,34,41,42}),3,4))


Regards
Claus Busch

 

[Ron Rosenfeld]

It does not make it easy. Your problem is not possible to solve as stated because you continue to define overlapping ranges.

By that I mean your table shows two different outcomes given the same value in E12.

Your Line 2. shows that 34 --> 3 point
but your Line 3. shows 34 --> 4 point

Also

Your Line 3. shows that 41 --> 4 point
but your Line 4. shows 41 --> 3 point.

If your break points are really as follows:

<33 0

33 to <35 3
35 to <41 4
41 to 42 3

42 0

then you could try:

=VLOOKUP(E12,{-1E+307,0;33,3;35,4;41,3;42.0000000000001,0},2)