If statement not returning the value i need

  • Thread starter Thread starter barbi b
  • Start date Start date
B

barbi b

please see following formula
=IF(M81>100<750,"6000100","4200710")&IF(M81>=750,"1201060","4200710")&IF(I81="TONER","6001000","4200710")
I need to return numbers in a cell if the $ is less than 100, return
4200710, greater than 100 but less than 750, return 6000100, and if >750,
return 1201060 and then if "I" column has the text "TONER" return 4700710.
this the result of my formula, i know i am close and i just missing the
"key" elements to get this to work..excel 2007
420071012010604200710
 
Hi Barbi,

Try:
=IF(M81<100,4200710,IF(M81<750,6000100,1201060))&IF(I81="TONER",6001000,4200710)
 
Okay i am almost there (Thank you by the way, you have no idea how long i
have been working on this bugger!!!)
=IF(M9>100<750,"6000100",IF(M9>=750,"1201060",IF(I9="TONER","4200710","6000100")))
SO, if Colum M9 & Has "TONER" on I9 is 150. it returns the correct amount,
but if Column M9 is $1500.00 then it returns 1201060
here is an example of the data i am working with:
TONER 150.00 4200710
HOLLY 150.00 6000100
TONER 15,000.00 1201060
I really can't thank you enough for your quick response.
I used to have curley hair before this...now it is straight!

macropod said:
Hi Barbi,

Try:
=IF(M81<100,4200710,IF(M81<750,6000100,1201060))&IF(I81="TONER",6001000,4200710)

--
Cheers
macropod
[MVP - Microsoft Word]


barbi b said:
please see following formula:
=IF(M81>100<750,"6000100","4200710")&IF(M81>=750,"1201060","4200710")&IF(I81="TONER","6001000","4200710")
I need to return numbers in a cell if the $ is less than 100, return
4200710, greater than 100 but less than 750, return 6000100, and if >750,
return 1201060 and then if "I" column has the text "TONER" return 4700710.
this the result of my formula, i know i am close and i just missing the
"key" elements to get this to work..excel 2007
420071012010604200710
Click to expand...
Click to expand...
 
The first thing you need to understand is that:
IF(M9>100<750 ...
is not a valid construct. If you mean "If M9 is between 100 and 750" then
you need to use:
=if(and(m9>100,m9<750),...

However, it's unlikely you need to use AND in your situation. When Excel's
If statement evaluates to True, it returns the True part of the formula and
quits. When you go at your situation in order, it becomes much simpler. Try
something like:
=if(m9<100,"what you want when M9 is less than
100",if(m9<750,6000100,1201060))

You don't have to check for M9 being greater than 750, because it won't get
there unless it is.

Now you need to explain what "TONER" has to do with your situation. In each
case (<100, <750, others), what do you want to happen when I9 equals
"TONER"?

Regards,
Fred

barbi b said:
Okay i am almost there (Thank you by the way, you have no idea how long i
have been working on this bugger!!!)
=IF(M9>100<750,"6000100",IF(M9>=750,"1201060",IF(I9="TONER","4200710","6000100")))
SO, if Colum M9 & Has "TONER" on I9 is 150. it returns the correct amount,
but if Column M9 is $1500.00 then it returns 1201060
here is an example of the data i am working with:
TONER 150.00 4200710
HOLLY 150.00 6000100
TONER 15,000.00 1201060
I really can't thank you enough for your quick response.
I used to have curley hair before this...now it is straight!

macropod said:
Hi Barbi,

Try:
=IF(M81<100,4200710,IF(M81<750,6000100,1201060))&IF(I81="TONER",6001000,4200710)

--
Cheers
macropod
[MVP - Microsoft Word]


barbi b said:
please see following formula:
=IF(M81>100<750,"6000100","4200710")&IF(M81>=750,"1201060","4200710")&IF(I81="TONER","6001000","4200710")
I need to return numbers in a cell if the $ is less than 100, return
4200710, greater than 100 but less than 750, return 6000100, and if
750,
return 1201060 and then if "I" column has the text "TONER" return
4700710.
this the result of my formula, i know i am close and i just missing the
"key" elements to get this to work..excel 2007
420071012010604200710
Click to expand...
Click to expand...
Click to expand...
 
Fred,
Thanks so much for your answer-it really helps. If I9 has "toner" in the
descp i need it to return a value of 4200710.
I really am very grateful for your input.
Thanks

Fred Smith said:
The first thing you need to understand is that:
IF(M9>100<750 ...
is not a valid construct. If you mean "If M9 is between 100 and 750" then
you need to use:
=if(and(m9>100,m9<750),...

However, it's unlikely you need to use AND in your situation. When Excel's
If statement evaluates to True, it returns the True part of the formula and
quits. When you go at your situation in order, it becomes much simpler. Try
something like:
=if(m9<100,"what you want when M9 is less than
100",if(m9<750,6000100,1201060))

You don't have to check for M9 being greater than 750, because it won't get
there unless it is.

Now you need to explain what "TONER" has to do with your situation. In each
case (<100, <750, others), what do you want to happen when I9 equals
"TONER"?

Regards,
Fred

barbi b said:
Okay i am almost there (Thank you by the way, you have no idea how long i
have been working on this bugger!!!)
=IF(M9>100<750,"6000100",IF(M9>=750,"1201060",IF(I9="TONER","4200710","6000100")))
SO, if Colum M9 & Has "TONER" on I9 is 150. it returns the correct amount,
but if Column M9 is $1500.00 then it returns 1201060
here is an example of the data i am working with:
TONER 150.00 4200710
HOLLY 150.00 6000100
TONER 15,000.00 1201060
I really can't thank you enough for your quick response.
I used to have curley hair before this...now it is straight!

macropod said:
Hi Barbi,

Try:
=IF(M81<100,4200710,IF(M81<750,6000100,1201060))&IF(I81="TONER",6001000,4200710)

--
Cheers
macropod
[MVP - Microsoft Word]


please see following formula:
=IF(M81>100<750,"6000100","4200710")&IF(M81>=750,"1201060","4200710")&IF(I81="TONER","6001000","4200710")
I need to return numbers in a cell if the $ is less than 100, return
4200710, greater than 100 but less than 750, return 6000100, and if
750,
return 1201060 and then if "I" column has the text "TONER" return
4700710.
this the result of my formula, i know i am close and i just missing the
"key" elements to get this to work..excel 2007
420071012010604200710
Click to expand...
Click to expand...
Click to expand...
 
Then try:

=if(i9="TONER",4200710,if(m9<100,"Less than
100",if(m9<750,6000100,1201060)))

Regards,
Fred

barbi b said:
Fred,
Thanks so much for your answer-it really helps. If I9 has "toner" in the
descp i need it to return a value of 4200710.
I really am very grateful for your input.
Thanks

Fred Smith said:
The first thing you need to understand is that:
IF(M9>100<750 ...
is not a valid construct. If you mean "If M9 is between 100 and 750" then
you need to use:
=if(and(m9>100,m9<750),...

However, it's unlikely you need to use AND in your situation. When
Excel's
If statement evaluates to True, it returns the True part of the formula
and
quits. When you go at your situation in order, it becomes much simpler.
Try
something like:
=if(m9<100,"what you want when M9 is less than
100",if(m9<750,6000100,1201060))

You don't have to check for M9 being greater than 750, because it won't
get
there unless it is.

Now you need to explain what "TONER" has to do with your situation. In
each
case (<100, <750, others), what do you want to happen when I9 equals
"TONER"?

Regards,
Fred

barbi b said:
Okay i am almost there (Thank you by the way, you have no idea how long
i
have been working on this bugger!!!)
=IF(M9>100<750,"6000100",IF(M9>=750,"1201060",IF(I9="TONER","4200710","6000100")))
SO, if Colum M9 & Has "TONER" on I9 is 150. it returns the correct
amount,
but if Column M9 is $1500.00 then it returns 1201060
here is an example of the data i am working with:
TONER 150.00 4200710
HOLLY 150.00 6000100
TONER 15,000.00 1201060
I really can't thank you enough for your quick response.
I used to have curley hair before this...now it is straight!

:

Hi Barbi,

Try:
=IF(M81<100,4200710,IF(M81<750,6000100,1201060))&IF(I81="TONER",6001000,4200710)

--
Cheers
macropod
[MVP - Microsoft Word]


please see following formula:
=IF(M81>100<750,"6000100","4200710")&IF(M81>=750,"1201060","4200710")&IF(I81="TONER","6001000","4200710")
I need to return numbers in a cell if the $ is less than 100, return
4200710, greater than 100 but less than 750, return 6000100, and if
750,
return 1201060 and then if "I" column has the text "TONER" return
4700710.
this the result of my formula, i know i am close and i just missing
the
"key" elements to get this to work..excel 2007
420071012010604200710
Click to expand...
Click to expand...
Click to expand...
 
Back
Top