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 41 it could fall within either of the two conditions and therefore could 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...........

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 :
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 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 :
Spencer101;1602317 Wrote:
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 41
it could fall within either of the two conditions and therefore could
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...........

On Sat, 2 Jun 2012 21:28:16 +0000, Abid Raza wrote:

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.

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$1232),3,0)"
BETWEEN 35 & 40 "=IF(AND(E$12<41,E$1234),4,0)"
BETWEEN 41 & 42 "=IF(AND(E$12<43,E$1240),3,0)"

Now lets assume your helper cells are Z1 to Z4

Z1 = "=IF(AND(E$12<35,E$1232),3,0)"
Z2 = "=IF(AND(E$12<41,E$1234),4,0)"
Z3 = "=IF(AND(E$12<43,E$1240),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.

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

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

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

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

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

Let me know if it works for you.

S.

On 3/06/2012 2:10 PM, Vacuum Sealed wrote:
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$1232),3,0)"
BETWEEN 35 & 40 "=IF(AND(E$12<41,E$1234),4,0)"
BETWEEN 41 & 42 "=IF(AND(E$12<43,E$1240),3,0)"

Now lets assume your helper cells are Z1 to Z4

Z1 = "=IF(AND(E$12<35,E$1232),3,0)"
Z2 = "=IF(AND(E$12<41,E$1234),4,0)"
Z3 = "=IF(AND(E$12<43,E$1240),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,E1232),3,IF(AND(E12<41,E1234),4,I F(AND(E12<43,E1240),3,0)))

HTH
Mick

Hi Mick,

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

=IF(AND(E12<35,E1232),3,IF(AND(E12<41,E1234),4,I F(AND(E12<43,E1240),3,0)))

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi,

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

=IF(OR(E12<33,E1242),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,E1242),0,IF(OR(E12={33,34,41,42}),3 ,4))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Sat, 02 Jun 2012 23:01:46 -0400, Ron Rosenfeld wrote:

On Sat, 2 Jun 2012 21:28:16 +0000, Abid Raza wrote:

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.

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)