Help With Formula

[Homerj24]

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<=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!

 

[Dav]

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

 

[Biff]

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

 

[Sandy Mann]

=CEILING(F6,4)/4-1

seems to do what you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[via135]

hi!

as far as i know
you can combine only 7 nested "IF"s!!!

-via135

 

[Biff]

If you want this instead:

IF(F6<=48,"11",IF(F6>48,"12")

=IF(F6>48,12,MAX(CEILING(F6/4,1)-1,0))

Biff

 

[Guest]

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]

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'v
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 wha
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

 

[Biff]

=CEILING(F6,4)/4-1

0 returns -1 and anything >48 continues to calculate.

Biff

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Homerj24]

Works great. Thanks Greg!

 

[Dav]

there are several ways round the problem, but it depends what th
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 neve
mind the butchered formula, as that is not important as u can fix i
later., will not get you the answer.

I repeat ask you specifc answer clearly

Regards

Da

 

[Guest]

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

 

[Biff]

=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

 

[Guest]

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

 

[Ron Rosenfeld]

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<=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

 

[Guest]

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