Trouble with "if"

  • Thread starter Thread starter flashpest
  • Start date Start date
F

flashpest

Hi,

I am trying to do an IF function on a cell that reads as follows:

=IF(C40<100,1,IF(C40>=100,2,IF(C40>=200,3,IF(C40>=300,4,IF(C4>=400,5)))))

It will only calculate the first 2 commands which means if i put 100 i
the generating cell, the target cell reads "1" and if I put 101 or 20
in the generating cell i get "2" but if you try 201 or 359 or 407 o
any highr number than 201 I get nothing but a "2" in the target cell
What am I doing wrong??

Advance thank
 
You need to put the criteria in descending order. Any number higher than 200
will also be higher thatn 100. Once that criterion is met, the subsequent
criteria will not be processed.
 
try inserting the AND function ...

=if(c40<100,1,if(and(c40>=100,c40<200),2,if(and(c40>=200,c40<300),3,if(and(c40>=300,c40<400),4,if(c40>=400,5)))))

there are simpler ways though
 
WOW! It worked like a charm! You are smart. How did you know how to d
this and why did the "and" have to be used?

While I have you here. I have a list on worksheet 2 which I pull up o
worksheet one with a drop down list. They are Ceramic installatio
descriptions such as: CERAMIC ON 1/4 WONDERBOARD/ NO PATTERN and RENAI
SUBFLOOR and so on and son on. It is a list of 30 or so items. What
want to do is when I pull up a Install description from the list an
select it for the cell I want its corresponding price to come up in th
next cell to the right. How do I do this. EG:

first cell
next cell
MARBLE ON 1/2 WONDERBOARD/ PATTERN 12.95



Also another one I could use help on: If I have a purchase order numbe
such as "11425869" dow do I extract the first 2 numbers, drop the las
6 numbers and put the first 2 extracted numbers in another cell wit
the digit "1" added to the beginning. For instance the resulting numbe
generated from the example above would be "111"


Thanks firefytr,
Eri
 
You might want to switch to a lookup formula...

=IF(C40<>"",VLOOKUP(C4,{0,1;100,2;200,3;300,4;400,5},2,1),"")
 
okay...

question 1:

just to the right of your drop-down boxes (assuming they are in a1), s
in b1...
=vlookup($a1,Sheet2!$a$1:$c$50,2,0)

and in c1...
=vlookup($a1,Sheet2!$a$1:$c$50,3,0)

and if you'd like to put one formula in b1 to copy over, then down:
=vlookup($a1,Sheet2!$a$1:$c$50,column(),0)
this formula assumes (w/ the column() ) that the layout is the exac
same in both sheets. eg a1:C50 on both sheets

this is also assuming your data goes down to row 50.
there are ways to get around expanding data if you wish also.
post back if necessary.




question 2:

=1&left(a1,2)
or whatever cell it is
 
Hi

=IF(C40>400,5,INT((C40+100)/100))
when C40 is always >=0. You may add a check for empty cell too, like in
Aladin's answer.
 
Just got up. Let me try all this stuff when I get home from work today
Guess what I do? - measure for Ceramic tile. Thanks for your help
I'll post late
 
Hi - checked your formula, and the problem is that in
the "second" IF layer, you're telling Excel to return a
value of 2 if C40>= 100. So any value in C40 above 100
will be true, and you will only get a 2 returned
regardless of the remaining IF statements. If you want
to return a certain number/text/etc. if a cell value
falls within a specific range, you'll need to put lower
AND upper boundaries on the statement (e.g., replace your
second and subsequent IF statements with something
structured as follows: IF(100<C40<200,2,IF
(200<C40<300,3, .....

Hope that helps.
 
The last one works great! But I still can't get that vlookup to work
firefytr do you mind sending me your e-mail address so I can send yo
the Excel file? That would be so awesome - I have spent countless hour
trying to figure out what you did in like 2 minutes.

Send it to me at (e-mail address removed) and I will forward you th
attachment.

Thanks again,
Eri
 
Back
Top