Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Using IF in a formula
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 128
Default Using IF in a formula
I am trying to enter multiple IF values into one cell. I enter all the values
and when I try to close out of the formula an error comes up and will not let
me. Do I have too many values? here is what I am trying to enter:


IF(C5<=0.49,"6",IF(C5<=0.99,"5.75",IF(C5<=1.49,"5. 5",IF(C5<=1.99,"5",IF(C5<=2.49,"4.75",IF(C5<=2.99, "4.75",IF(C5<=3.99,"4.5",IF(C5<=4.99,"4.375",IF(C5 <=5.99,"4.25",IF(C5<=6.99,"4.125",IF(C5<=7.99,"4", IF(C5<=8.99,"3.75",IF(C5<=9.99,"3.625",IF(C5<=19.9 9,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",I F(C5<=49.99,"3.125",IF(C5<=59.99,"3",IF(C5<=69.99, "2.75",IF(C5<=79.99,"2.625",IF(C5<=89.99,"2.5",IF( C5<=99.99,"2.3332",IF(C5<=139.99,"2.2499",IF(C5<=1 69.99,"2.1666",IF(C5<=199.99,"2",IF(C5<=239.99,"1. 855",IF(C5<=269.99,"1.8225",IF(C5<=299.99,"1.7862" ,IF(C5<=349.99,"1.75",IF(C5=399.99,"1.725",IF(C5<= 499.99,"1.6875",IF(C5<=749.99,"1.6",IF(C5<=999.99, "1.55",IF(C5<=1499.99,"1.5",IF(C5<=1999.99,"1.45", IF(C5<=2999.99,"1.4",IF(C5<=4999.99,"1.35",IF(C5<= 9999.99,"1.3375",IF(C5<=24999.99,"1.3333",IF(C5<=4 9999.00,"1.33"))


....any suggestions? THANKS!!!!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Using IF in a formula
You are limited to 7 nested "IF's"

For your purposes a lookup table would be more suitable.

Two column table and a VLOOKUP formula.

See Debra Dalgleish's site for excellent instructions on VLOOKUP formulas and a
downloadable sample workbook to assist.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben MS Excel MVP

On Thu, 28 Sep 2006 11:13:01 -0700, kris wrote:

I am trying to enter multiple IF values into one cell. I enter all the values
and when I try to close out of the formula an error comes up and will not let
me. Do I have too many values? here is what I am trying to enter:


IF(C5<=0.49,"6",IF(C5<=0.99,"5.75",IF(C5<=1.49,"5 .5",IF(C5<=1.99,"5",IF(C5<=2.49,"4.75",IF(C5<=2.99 ,"4.75",IF(C5<=3.99,"4.5",IF(C5<=4.99,"4.375",IF(C 5<=5.99,"4.25",IF(C5<=6.99,"4.125",IF(C5<=7.99,"4" ,IF(C5<=8.99,"3.75",IF(C5<=9.99,"3.625",IF(C5<=19. 99,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25", IF(C5<=49.99,"3.125",IF(C5<=59.99,"3",IF(C5<=69.99 ,"2.75",IF(C5<=79.99,"2.625",IF(C5<=89.99,"2.5",IF (C5<=99.99,"2.3332",IF(C5<=139.99,"2.2499",IF(C5<= 169.99,"2.1666",IF(C5<=199.99,"2",IF(C5<=239.99,"1 .855",IF(C5<=269.99,"1.8225",IF(C5<=299.99,"1.7862 ",IF(C5<=349.99,"1.75",IF(C5=399.99,"1.725",IF(C5< =499.99,"1.6875",IF(C5<=749.99,"1.6",IF(C5<=999.99 ,"1.55",IF(C5<=1499.99,"1.5",IF(C5<=1999.99,"1.45" ,IF(C5<=2999.99,"1.4",IF(C5<=4999.99,"1.35",IF(C5< =9999.99,"1.3375",IF(C5<=24999.99,"1.3333",IF(C5<= 49999.00,"1.33"))


...any suggestions? THANKS!!!!

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 79
Default Using IF in a formula
Did you try to use vlookup?

F G H
min max result
0 0,49 6
0,5 0,99 5,75
1 1,49 5,5
....

=VLOOKUP(C2, F2:H5, 3, 1)

Rodrigo Ferreira

--
"kris" escreveu na mensagem
...
I am trying to enter multiple IF values into one cell. I enter all the
values
and when I try to close out of the formula an error comes up and will not
let
me. Do I have too many values? here is what I am trying to enter:


IF(C5<=0.49,"6",IF(C5<=0.99,"5.75",IF(C5<=1.49,"5. 5",IF(C5<=1.99,"5",IF(C5<=2.49,"4.75",IF(C5<=2.99, "4.75",IF(C5<=3.99,"4.5",IF(C5<=4.99,"4.375",IF(C5 <=5.99,"4.25",IF(C5<=6.99,"4.125",IF(C5<=7.99,"4", IF(C5<=8.99,"3.75",IF(C5<=9.99,"3.625",IF(C5<=19.9 9,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",I F(C5<=49.99,"3.125",IF(C5<=59.99,"3",IF(C5<=69.99, "2.75",IF(C5<=79.99,"2.625",IF(C5<=89.99,"2.5",IF( C5<=99.99,"2.3332",IF(C5<=139.99,"2.2499",IF(C5<=1 69.99,"2.1666",IF(C5<=199.99,"2",IF(C5<=239.99,"1. 855",IF(C5<=269.99,"1.8225",IF(C5<=299.99,"1.7862" ,IF(C5<=349.99,"1.75",IF(C5=399.99,"1.725",IF(C5<= 499.99,"1.6875",IF(C5<=749.99,"1.6",IF(C5<=999.99, "1.55",IF(C5<=1499.99,"1.5",IF(C5<=1999.99,"1.45", IF(C5<=2999.99,"1.4",IF(C5<=4999.99,"1.35",IF(C5<= 9999.99,"1.3375",IF(C5<=24999.99,"1.3333",IF(C5<=4 9999.00,"1.33"))


...any suggestions? THANKS!!!!


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,118
Default Using IF in a formula
Try something like this:

Enter this table in a range (I'll assume E1:F40)
0 6.000
0.50 5.750
1.00 5.500
1.50 5.000
2.00 4.750
2.50 4.750
3.00 4.500
4.00 4.375
5.00 4.250
6.00 4.125
7.00 4.000
8.00 3.750
9.00 3.625
10.00 3.500
20.00 3.375
30.00 3.250
40.00 3.125
50.00 3.000
60.00 2.750
70.00 2.625
80.00 2.500
90.00 2.333
100.00 2.250
140.00 2.167
170.00 2.000
200.00 1.855
240.00 1.823
270.00 1.786
300.00 1.750
350.00 1.725
400.00 1.688
500.00 1.600
750.00 1.550
1000.00 1.500
1500.00 1.450
2000.00 1.400
3000.00 1.350
5000.00 1.338
10000.00 1.333
25000.00 1.330

Then
For a value in C5

F5: =VLOOKUP(C5,$E$1:$F$40,2,1)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"kris" wrote:

I am trying to enter multiple IF values into one cell. I enter all the values
and when I try to close out of the formula an error comes up and will not let
me. Do I have too many values? here is what I am trying to enter:


IF(C5<=0.49,"6",IF(C5<=0.99,"5.75",IF(C5<=1.49,"5. 5",IF(C5<=1.99,"5",IF(C5<=2.49,"4.75",IF(C5<=2.99, "4.75",IF(C5<=3.99,"4.5",IF(C5<=4.99,"4.375",IF(C5 <=5.99,"4.25",IF(C5<=6.99,"4.125",IF(C5<=7.99,"4", IF(C5<=8.99,"3.75",IF(C5<=9.99,"3.625",IF(C5<=19.9 9,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",I F(C5<=49.99,"3.125",IF(C5<=59.99,"3",IF(C5<=69.99, "2.75",IF(C5<=79.99,"2.625",IF(C5<=89.99,"2.5",IF( C5<=99.99,"2.3332",IF(C5<=139.99,"2.2499",IF(C5<=1 69.99,"2.1666",IF(C5<=199.99,"2",IF(C5<=239.99,"1. 855",IF(C5<=269.99,"1.8225",IF(C5<=299.99,"1.7862" ,IF(C5<=349.99,"1.75",IF(C5=399.99,"1.725",IF(C5<= 499.99,"1.6875",IF(C5<=749.99,"1.6",IF(C5<=999.99, "1.55",IF(C5<=1499.99,"1.5",IF(C5<=1999.99,"1.45", IF(C5<=2999.99,"1.4",IF(C5<=4999.99,"1.35",IF(C5<= 9999.99,"1.3375",IF(C5<=24999.99,"1.3333",IF(C5<=4 9999.00,"1.33"))


...any suggestions? THANKS!!!!
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
Avi Avi is offline
external usenet poster
  
Posts: 9
Default Using IF in a formula
Hi Kris

I think there are too many conditions - Please see this information from Ms
Excel Help:

Up to seven IF functions can be nested as value_if_true and value_if_false
arguments to construct more elaborate tests. If you want to test more than
seven conditions, consider using the LOOKUP, VLOOKUP, or HLOOKUP function.

I suggest that you create a list of values and then use Vlookup to pick up
this values.

Hope this helps









"kris" wrote:

I am trying to enter multiple IF values into one cell. I enter all the values
and when I try to close out of the formula an error comes up and will not let
me. Do I have too many values? here is what I am trying to enter:


IF(C5<=0.49,"6",IF(C5<=0.99,"5.75",IF(C5<=1.49,"5. 5",IF(C5<=1.99,"5",IF(C5<=2.49,"4.75",IF(C5<=2.99, "4.75",IF(C5<=3.99,"4.5",IF(C5<=4.99,"4.375",IF(C5 <=5.99,"4.25",IF(C5<=6.99,"4.125",IF(C5<=7.99,"4", IF(C5<=8.99,"3.75",IF(C5<=9.99,"3.625",IF(C5<=19.9 9,"3.5",IF(C5<=29.99,"3.375",IF(C5<=39.99,"3.25",I F(C5<=49.99,"3.125",IF(C5<=59.99,"3",IF(C5<=69.99, "2.75",IF(C5<=79.99,"2.625",IF(C5<=89.99,"2.5",IF( C5<=99.99,"2.3332",IF(C5<=139.99,"2.2499",IF(C5<=1 69.99,"2.1666",IF(C5<=199.99,"2",IF(C5<=239.99,"1. 855",IF(C5<=269.99,"1.8225",IF(C5<=299.99,"1.7862" ,IF(C5<=349.99,"1.75",IF(C5=399.99,"1.725",IF(C5<= 499.99,"1.6875",IF(C5<=749.99,"1.6",IF(C5<=999.99, "1.55",IF(C5<=1499.99,"1.5",IF(C5<=1999.99,"1.45", IF(C5<=2999.99,"1.4",IF(C5<=4999.99,"1.35",IF(C5<= 9999.99,"1.3375",IF(C5<=24999.99,"1.3333",IF(C5<=4 9999.00,"1.33"))


...any suggestions? THANKS!!!!
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Reusing formula Tony29 Excel Discussion (Misc queries) 7 September 7th 06 03:34 AM
2 Nesting questions Starchaser Excel Worksheet Functions 7 January 20th 06 06:53 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 05:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 05:18 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM


All times are GMT +1. The time now is 09:18 PM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement