OK, I am not sure how to even begin to search for this because I am unsure of
the nomenclature I need to look for. That said, here is what I am looking to
understand:

I need to display a value of "1" if a total (manually entered in an adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if the
entered value is 25, the value displayed in the adjacent cell would be "1".

Can and how do I do this?
Many thanks in advance!

Hi Chris,

Option 1:
Create a table, say in range A1:B3, like this:

20 1
30 2
40 3

if in cell C1 you manually introduce a serched value, then in cell D1 input
the following formula:

=VLOOKUP(C1,$A$1:$B$3,2)

Option 2:
No table necessary, just use the following formula:

=VLOOKUP(C1,{20,1;30,2;40,3},2)

Option 3:
No table necessary, just use the following formula:

=INDEX({1,2,3},MATCH(C1,{20;30;40}))

Regards,
KL

"Chris McDannold" <Chris wrote in
message ...
OK, I am not sure how to even begin to search for this because I am unsure
of
the nomenclature I need to look for. That said, here is what I am looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if
the
entered value is 25, the value displayed in the adjacent cell would be
"1".

Can and how do I do this?
Many thanks in advance!

Wonderful! However, please see the response to Biff below as I am having the
same problem when entering your formula.
Thanks again!

"KL" wrote:

Hi Chris,

Option 1:
Create a table, say in range A1:B3, like this:

20 1
30 2
40 3

if in cell C1 you manually introduce a serched value, then in cell D1 input
the following formula:

=VLOOKUP(C1,$A$1:$B$3,2)

Option 2:
No table necessary, just use the following formula:

=VLOOKUP(C1,{20,1;30,2;40,3},2)

Option 3:
No table necessary, just use the following formula:

=INDEX({1,2,3},MATCH(C1,{20;30;40}))

Regards,
KL

"Chris McDannold" <Chris wrote in
message ...
OK, I am not sure how to even begin to search for this because I am unsure
of
the nomenclature I need to look for. That said, here is what I am looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if
the
entered value is 25, the value displayed in the adjacent cell would be
"1".

Can and how do I do this?
Many thanks in advance!

Can't see the response to Biff :-(
Have you already posted it?

KL


"Chris McDannold" wrote in
message ...
Wonderful! However, please see the response to Biff below as I am having
the
same problem when entering your formula.
Thanks again!

"KL" wrote:

Hi Chris,

Option 1:
Create a table, say in range A1:B3, like this:

20 1
30 2
40 3

if in cell C1 you manually introduce a serched value, then in cell D1
input
the following formula:

=VLOOKUP(C1,$A$1:$B$3,2)

Option 2:
No table necessary, just use the following formula:

=VLOOKUP(C1,{20,1;30,2;40,3},2)

Option 3:
No table necessary, just use the following formula:

=INDEX({1,2,3},MATCH(C1,{20;30;40}))

Regards,
KL

"Chris McDannold" <Chris wrote in
message ...
OK, I am not sure how to even begin to search for this because I am
unsure
of
the nomenclature I need to look for. That said, here is what I am
looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So
if
the
entered value is 25, the value displayed in the adjacent cell would be
"1".

Can and how do I do this?
Many thanks in advance!

Yes, but here is the text of the response. The formula he suggested is as
follows:
=IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,0)))

Response:
This is great! I have a problem though. The desired value does not display in
the cell this formula is entered into.

If I enter 23 in A1, all I see in A2 (where the forumla is located) is the
forumla itself. Looking at the formula's logical test, the only time
"Value_if_false" is reported with a value is when A1<20. How do I set it so
it only shows what is true, and disregards a false result EXCEPT when A1<20?

Does that make sense?


"KL" wrote:

Can't see the response to Biff :-(
Have you already posted it?

KL


"Chris McDannold" wrote in
message ...
Wonderful! However, please see the response to Biff below as I am having
the
same problem when entering your formula.
Thanks again!

"KL" wrote:

Hi Chris,

Option 1:
Create a table, say in range A1:B3, like this:

20 1
30 2
40 3

if in cell C1 you manually introduce a serched value, then in cell D1
input
the following formula:

=VLOOKUP(C1,$A$1:$B$3,2)

Option 2:
No table necessary, just use the following formula:

=VLOOKUP(C1,{20,1;30,2;40,3},2)

Option 3:
No table necessary, just use the following formula:

=INDEX({1,2,3},MATCH(C1,{20;30;40}))

Regards,
KL

"Chris McDannold" <Chris wrote in
message ...
OK, I am not sure how to even begin to search for this because I am
unsure
of
the nomenclature I need to look for. That said, here is what I am
looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So
if
the
entered value is 25, the value displayed in the adjacent cell would be
"1".

Can and how do I do this?
Many thanks in advance!

Chris,

Try going the menu ToolsOptions, selecting the 'View' tab, and uncheking
the 'Formulas' option at the bottom part of the dialog.

Regards,
KL


"Chris McDannold" wrote in
message ...
Wonderful! However, please see the response to Biff below as I am having
the
same problem when entering your formula.
Thanks again!

"KL" wrote:

Hi Chris,

Option 1:
Create a table, say in range A1:B3, like this:

20 1
30 2
40 3

if in cell C1 you manually introduce a serched value, then in cell D1
input
the following formula:

=VLOOKUP(C1,$A$1:$B$3,2)

Option 2:
No table necessary, just use the following formula:

=VLOOKUP(C1,{20,1;30,2;40,3},2)

Option 3:
No table necessary, just use the following formula:

=INDEX({1,2,3},MATCH(C1,{20;30;40}))

Regards,
KL

"Chris McDannold" <Chris wrote in
message ...
OK, I am not sure how to even begin to search for this because I am
unsure
of
the nomenclature I need to look for. That said, here is what I am
looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So
if
the
entered value is 25, the value displayed in the adjacent cell would be
"1".

Can and how do I do this?
Many thanks in advance!

Yeah, I thought of that too.

"KL" wrote:

Chris,

Try going the menu ToolsOptions, selecting the 'View' tab, and uncheking
the 'Formulas' option at the bottom part of the dialog.

Regards,
KL


"Chris McDannold" wrote in
message ...
Wonderful! However, please see the response to Biff below as I am having
the
same problem when entering your formula.
Thanks again!

"KL" wrote:

Hi Chris,

Option 1:
Create a table, say in range A1:B3, like this:

20 1
30 2
40 3

if in cell C1 you manually introduce a serched value, then in cell D1
input
the following formula:

=VLOOKUP(C1,$A$1:$B$3,2)

Option 2:
No table necessary, just use the following formula:

=VLOOKUP(C1,{20,1;30,2;40,3},2)

Option 3:
No table necessary, just use the following formula:

=INDEX({1,2,3},MATCH(C1,{20;30;40}))

Regards,
KL

"Chris McDannold" <Chris wrote in
message ...
OK, I am not sure how to even begin to search for this because I am
unsure
of
the nomenclature I need to look for. That said, here is what I am
looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So
if
the
entered value is 25, the value displayed in the adjacent cell would be
"1".

Can and how do I do this?
Many thanks in advance!

Hi Chris,

Does your answer mean it worked or not?

Regards,
KL


"Chris McDannold" wrote in
message ...
Yeah, I thought of that too.

"KL" wrote:

Chris,

Try going the menu ToolsOptions, selecting the 'View' tab, and uncheking
the 'Formulas' option at the bottom part of the dialog.

Regards,
KL


"Chris McDannold" wrote in
message ...
Wonderful! However, please see the response to Biff below as I am
having
the
same problem when entering your formula.
Thanks again!

"KL" wrote:

Hi Chris,

Option 1:
Create a table, say in range A1:B3, like this:

20 1
30 2
40 3

if in cell C1 you manually introduce a serched value, then in cell D1
input
the following formula:

=VLOOKUP(C1,$A$1:$B$3,2)

Option 2:
No table necessary, just use the following formula:

=VLOOKUP(C1,{20,1;30,2;40,3},2)

Option 3:
No table necessary, just use the following formula:

=INDEX({1,2,3},MATCH(C1,{20;30;40}))

Regards,
KL

"Chris McDannold" <Chris wrote in
message ...
OK, I am not sure how to even begin to search for this because I am
unsure
of
the nomenclature I need to look for. That said, here is what I am
looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+.
So
if
the
entered value is 25, the value displayed in the adjacent cell would
be
"1".

Can and how do I do this?
Many thanks in advance!

Assuming that 29.8 is still mapped onto 1 and disregarding a simpler
rounding solution for a moment...

=LOOKUP(A2,{-9.99999999999999E+307,0;20,1;30,2;40,3;"
",0})

would cover a bit more that is desirable to have.

Note that "
" is the result of =CHAR(1).

KL wrote:
Hi Chris,

Option 1:
Create a table, say in range A1:B3, like this:

20 1
30 2
40 3

if in cell C1 you manually introduce a serched value, then in cell D1 input
the following formula:

=VLOOKUP(C1,$A$1:$B$3,2)

Option 2:
No table necessary, just use the following formula:

=VLOOKUP(C1,{20,1;30,2;40,3},2)

Option 3:
No table necessary, just use the following formula:

=INDEX({1,2,3},MATCH(C1,{20;30;40}))

Regards,
KL

"Chris McDannold" <Chris wrote in
message ...

OK, I am not sure how to even begin to search for this because I am unsure
of
the nomenclature I need to look for. That said, here is what I am looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if
the
entered value is 25, the value displayed in the adjacent cell would be
"1".

Can and how do I do this?
Many thanks in advance!




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Hi!

Assume the value is entered in A1:

=IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,0)))

You didn't specify what to do if A1 < 20 so I just used a zero for that
condition. If instead, you want the cell to be blank:

=IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,"")))

Biff

"Chris McDannold" <Chris wrote in
message ...
OK, I am not sure how to even begin to search for this because I am unsure
of
the nomenclature I need to look for. That said, here is what I am looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if
the
entered value is 25, the value displayed in the adjacent cell would be
"1".

Can and how do I do this?
Many thanks in advance!

This is great! I have a problem though. The desired value does not display in
the cell this formula is entered into.

If I enter 23 in A1, all I see in A2 (where the forumla is located) is the
forumla itself. Looking at the formula's logical test, the only time
"Value_if_false" is reported with a value is when A1<20. How do I set it so
it only shows what is true, and disregards a false result EXCEPT when A1<20?

Does that make sense?

"Biff" wrote:

Hi!

Assume the value is entered in A1:

=IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,0)))

You didn't specify what to do if A1 < 20 so I just used a zero for that
condition. If instead, you want the cell to be blank:

=IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,"")))

Biff

"Chris McDannold" <Chris wrote in
message ...
OK, I am not sure how to even begin to search for this because I am unsure
of
the nomenclature I need to look for. That said, here is what I am looking
to
understand:

I need to display a value of "1" if a total (manually entered in an
adjacent
cell) is 20-29; "2" if a total is 30-39; and "3" if a total is 40+. So if
the
entered value is 25, the value displayed in the adjacent cell would be
"1".

Can and how do I do this?
Many thanks in advance!

Good morning Chris McDannold

This formula will do what you want by checking the cell A1 (you haven't
specified). You didn't mention what is to return if the value of the
cell being checked is less than 20. In the example below a zero is
returned.

=IF(A1=40,3,IF(A1=30,2,IF(A1=20,1,0)))

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=395528