Help With Formula

H

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

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
 
B

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
 
S

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
 
B

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
 
G

Greg Wilson

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
 
H

Homerj24

via135 said:
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
 
S

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
 
D

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
 
G

Greg Wilson

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
 
B

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
 
G

Greg Wilson

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
 
R

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
 
G

Greg Wilson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top