| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Formula
Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=16 ,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<=4 4,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
well you can combine cells but what do u want to do? currently your numbers are text rather than numbers I am not sure you want that, but what does trunc((f6-4)/4) do, it appears to be the same as you function or text(trunc((f6-4)/4),0) if you really want numbers Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
Hi!
There's a problem with your logic..... IF(F6<=48,"11",IF(F6=52,"12") What do you want when the value is 48 but <52 ? Biff "Homerj24" wrote in message ... Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=16 ,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<=4 4,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
hi! as far as i know you can combine only 7 nested "IF"s!!! -via135 Homerj24 Wrote: Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=16 ,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<=4 4,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
If you want this instead:
IF(F6<=48,"11",IF(F648,"12") =IF(F648,12,MAX(CEILING(F6/4,1)-1,0)) Biff "Biff" wrote in message ... Hi! There's a problem with your logic..... IF(F6<=48,"11",IF(F6=52,"12") What do you want when the value is 48 but <52 ? Biff "Homerj24" wrote in message ... Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=16 ,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<=4 4,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
You have too many nesting levels - max 7. The appended formula should return
the desired numbers. =IF(F6/4<2, 0, INT(F6/4)-1) Regards, Greg "Homerj24" wrote: Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=16 ,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<=4 4,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
via135 Wrote: hi! as far as i know you can combine only 7 nested "IF"s!!! -via135 And this is my main problem. Nevermind the butchered formula that I've copied and pasted 40k times in the past 2 days. I can fix that later. If I split the formulas in half with no more than 7 IF's, it does what I want it to do. But because it's more than 7, Excel won't accept it. There has to be a way around this, but I can't figure it out. -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
=CEILING(F6,4)/4-1
0 returns -1 and anything 48 continues to calculate. Biff "Sandy Mann" wrote in message ... =CEILING(F6,4)/4-1 seems to do what you want. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Homerj24" wrote in message ... Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=16 ,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<=4 4,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
want that, but what does trunc((f6-4)/4) do, it appears to be the same
not quite, with F6= anything up to 7, trunc((f6-4)/4) returns 0 For my part I missed the =52 part until I saw Biff's post. Assuming that the OP mean everyting above 48 to return 12 then I would add to my formula: =MIN(CEILING(F6,4)/4-1,12) -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Dav" wrote in message ... well you can combine cells but what do u want to do? currently your numbers are text rather than numbers I am not sure you want that, but what does trunc((f6-4)/4) do, it appears to be the same as you function or text(trunc((f6-4)/4),0) if you really want numbers Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
Works great. Thanks Greg! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
there are several ways round the problem, but it depends what the problem is. Which is why you need to state the problem clearly. For people to answer your post with a solution only to be told never mind the butchered formula, as that is not important as u can fix it later., will not get you the answer. I repeat ask you specifc answer clearly Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
You're welcome. To be fair to Dav, his formula also works except with the
minor issue that it returns -1 if F6 = 0. This would be an easy fix. Both formula keep going when F6 52. I thought you might want this anyway or it wouldn't be a problem. If it's a problem it's an easy fix. Greg "Homerj24" wrote: Works great. Thanks Greg! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
=IF(F6/4<2, 0, INT(F6/4)-1)
Anything <8 returns 0, anything 52 continues to calculate. But the OP says it "works great", so........ Biff "Greg Wilson" wrote in message ... You have too many nesting levels - max 7. The appended formula should return the desired numbers. =IF(F6/4<2, 0, INT(F6/4)-1) Regards, Greg "Homerj24" wrote: Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=16 ,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<=4 4,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
To resolve the issues that Biff has raised, perhaps this:
=IF(F6/4<=1.5, 0, MIN(INT((F6+3)/4)-1, 12)) Regards, Greg "Homerj24" wrote: Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=16 ,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<=4 4,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
On Sat, 22 Jul 2006 13:20:57 -0400, Homerj24
wrote: Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=1 6,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<= 44,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! A different approach. For your example: =MIN(12,FLOOR(MAX(0,(F6-1)/4),1)) should work. In other instances, VLOOKUP can be useful. --ron |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
I misread the post. All my posts assumed the first requirement was F6<=6
instead of F6<=4. Going cross-eyed I guess. IMHO Ron has the best answer. Greg "Greg Wilson" wrote: To resolve the issues that Biff has raised, perhaps this: =IF(F6/4<=1.5, 0, MIN(INT((F6+3)/4)-1, 12)) Regards, Greg "Homerj24" wrote: Hello- Here is my current forumula: =IF(F6<=4,"0",IF(F6<=8,"1",IF(F6<=12,"2",IF(F6<=16 ,"3",IF(F6<=20,"4",IF(F6<=24,"5",IF(F6<=28,"6", IF(F6<=32,"7",IF(F6<=36,"8",IF(F6<=40,"9",IF(F6<=4 4,"10",IF(F6<=48,"11",IF(F6=52,"12")))))))))))) ) I am only allowed to use a certain number of commands in a formula, correct? How do I get around that? Any help would be greatly appriciated. Thanks! -- Homerj24 ------------------------------------------------------------------------ Homerj24's Profile: http://www.excelforum.com/member.php...o&userid=36652 View this thread: http://www.excelforum.com/showthread...hreadid=564001 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Creating a check mark box | Setting up and Configuration of Excel | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
