Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Larry
 
Posts: n/a
Default How do create a formula to evalute a # to return 1 of 4 conditions

I have to evaluate a single number in a single cell and return a value based
on that number. How do I build a formula to do the following?
If the number is < 500, I need to return "Non-Target"
If the number is = 500 but < 1500, I need to return "Low"
If the number is = 1500 but < 3500, I need to return "Medium"
If the number is = 3500, I need to return a value of "High"

How do I build this formula
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

One way, assume the number in question is in cell A1


=IF(A1="","",VLOOKUP(A1,{0,"Non
Target";500,"Low";1500,"Medium";3500,"High"},2))

I assumed that the number can't be less than zero

--
Regards,

Peo Sjoblom


"Larry" wrote in message
...
I have to evaluate a single number in a single cell and return a value
based
on that number. How do I build a formula to do the following?
If the number is < 500, I need to return "Non-Target"
If the number is = 500 but < 1500, I need to return "Low"
If the number is = 1500 but < 3500, I need to return "Medium"
If the number is = 3500, I need to return a value of "High"

How do I build this formula


  #3   Report Post  
Larry
 
Posts: n/a
Default

Peo, you are Fantastic!!!.

I understand some of what you did, but can you explain, in simple terms,
what is occurring in this formula?

Thanks
LB

"Peo Sjoblom" wrote:

One way, assume the number in question is in cell A1


=IF(A1="","",VLOOKUP(A1,{0,"Non
Target";500,"Low";1500,"Medium";3500,"High"},2))

I assumed that the number can't be less than zero

--
Regards,

Peo Sjoblom


"Larry" wrote in message
...
I have to evaluate a single number in a single cell and return a value
based
on that number. How do I build a formula to do the following?
If the number is < 500, I need to return "Non-Target"
If the number is = 500 but < 1500, I need to return "Low"
If the number is = 1500 but < 3500, I need to return "Medium"
If the number is = 3500, I need to return a value of "High"

How do I build this formula



  #4   Report Post  
paul
 
Posts: n/a
Default

=IF(A1=0,0,IF(A1<500,"non
target",IF(A1<1500,"low",IF(A1<3500,"medium","high "))))
because your sequence is "logical"you can use a simple "if",because it moves
to the next argument as soon as the previous one is not true. You may or may
not need the initial if( a1 is o argument,it will display high if cell a1 is
empty or 0 without it

--
paul
remove nospam for email addy!



"Larry" wrote:

I have to evaluate a single number in a single cell and return a value based
on that number. How do I build a formula to do the following?
If the number is < 500, I need to return "Non-Target"
If the number is = 500 but < 1500, I need to return "Low"
If the number is = 1500 but < 3500, I need to return "Medium"
If the number is = 3500, I need to return a value of "High"

How do I build this formula

  #5   Report Post  
paul
 
Posts: n/a
Default

vlookup is a function that you use to look at a column of information and
return a value from the same row a specified number of columns across to the
right
normally you would have a little table
0 non target
500 low
1500 medium
3500 high
but because its so simple peo used an array constant to represent the table.
so his formula says if a1 is blank return blank otherwise look in the first
column for a value and return the value next to it (in the second column)


--
paul
remove nospam for email addy!



"Larry" wrote:

Peo, you are Fantastic!!!.

I understand some of what you did, but can you explain, in simple terms,
what is occurring in this formula?

Thanks
LB

"Peo Sjoblom" wrote:

One way, assume the number in question is in cell A1


=IF(A1="","",VLOOKUP(A1,{0,"Non
Target";500,"Low";1500,"Medium";3500,"High"},2))

I assumed that the number can't be less than zero

--
Regards,

Peo Sjoblom


"Larry" wrote in message
...
I have to evaluate a single number in a single cell and return a value
based
on that number. How do I build a formula to do the following?
If the number is < 500, I need to return "Non-Target"
If the number is = 500 but < 1500, I need to return "Low"
If the number is = 1500 but < 3500, I need to return "Medium"
If the number is = 3500, I need to return a value of "High"

How do I build this formula



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can create a formula to add data from 1 worksheet to another? Linnie Excel Discussion (Misc queries) 3 June 24th 05 05:36 PM
Why does my formula return zero? was Excel Discussion (Misc queries) 11 May 26th 05 09:49 PM
how to create a multiple conditional formula Ad Buijs Excel Discussion (Misc queries) 3 April 20th 05 09:41 PM
create a chart with a formula anon Charts and Charting in Excel 1 December 15th 04 09:55 PM
How do I create a formula in Excel that will countif or sumif bef. bkclark Excel Worksheet Functions 4 November 10th 04 06:30 PM


All times are GMT +1. The time now is 09:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"