Display single value for multiple ranges?

C

Chris McDannold

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!
 
K

KL

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
 
B

Biff

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
 
C

Chris McDannold

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

dominicb

Good morning Chris McDannold

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

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

HTH

Dominic
 
C

Chris McDannold

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?
 
K

KL

Chris,

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

Regards,
KL
 
C

Chris McDannold

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:
 
C

Chris McDannold

Yeah, I thought of that too.

KL said:
Chris,

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

Regards,
KL
Click to expand...
 
K

KL

Then try the following:

1) select the cell that contain the formula
2) go to menu Format>Cells...
3) on the 'Number' tab choose 'General' from the 'Category' list and hit
'OK'
4) clic inside the formula bar and hit 'Enter'

Any changes?

Regards,
KL
 
C

Chris McDannold

That got it. Thanks so much!

KL said:
Then try the following:

1) select the cell that contain the formula
2) go to menu Format>Cells...
3) on the 'Number' tab choose 'General' from the 'Category' list and hit
'OK'
4) clic inside the formula bar and hit 'Enter'

Any changes?

Regards,
KL
Click to expand...
 
A

Aladin Akyurek

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).
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
Click to expand...

--

[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.
 
Top