Using "" in the same formula

Posted by Christy on February 13, 2002 10:34 PM

I am trying to get a formula to perform the following:
"If b5 is less than 43 then /1; if b5 is greater than 43 but less than 85 then /2. This goes on in multiples of 42 but if I get the first two, I can add the rest(she says with the utmost confidence). The same cell which contains this formula (B7) is where I'd like the outcome to show, if possible. Any help would be awesome - I've now pulled ALL my hair out!!
Thank you so much!
~Christy

Posted by Christy on February 13, 2002 10:38 PM

The above should say "using Greater than and Less than in the same formula"!

Posted by Phil Ridley on February 13, 2002 11:42 PM

You could try this :
=IF(B5>85,"",IF(B5>43,"DO THIS","DO THAT"))

Depending on how many multiples of 42 you want, this may work. However Excel can only nest seven levels and the formula becomes unweildy.
Hope this helps.

Phil.

Posted by Phil Ridley on February 13, 2002 11:49 PM

Or by "If b5 is less than 43 then /1" you mean divide by one etc, this will work better :

=A11/ROUNDUP(A11/42,0)

Sorry, I misunderstood the original question :)

Posted by Aladin Akyurek on February 14, 2002 1:22 AM

Re: The above should say "using Greater than and Less than in the same formula"!

=(A1&LT;=43)+(A1>43)*ROUNDUP(((A1-43)/42)+1,0)

what you're looking for?

===========

Posted by Christy on February 14, 2002 6:48 AM

Re: The above should say "using Greater than and Less than in the same formula"!

Posted by Christy on February 14, 2002 6:52 AM

Yes, Aladin - that is perfect!! Thank you so much!! Thank also to Phil!

Yes, Aladin - that is perfect!! I never imagined it could be that simple!!!!
Thank you so much!! Thanks also to Phil!
(sorry about the previous entry, I hit "enter too many times after my name!)

Posted by Christy on February 14, 2002 7:30 AM

SORRY! ! I answered too quickly!! Let me clarify . . .

When the formula does the division, it needs to show the result of the division, not the number it divided by. i.e.:
(formula in cell B12) "if A1 is less than 43 then divide by 1; if A1 is greater than 43 but less than 85 then divide by 2; if A1 is greater than 85 but less than 127 then divide by 3"
So, if A1 contains the number 24, the number in cell B12 should now be 24
If A1 contains the number 122, the number in cell B12 should be 40.67.
Thank you sooo much! ! Thanks also to Phil!

Posted by Aladin Akyurek on February 14, 2002 8:12 AM

Kristy: Clarified specs require

a simple modification to what we have:

=(A1&LT;=43)+(A1>43)*A1/ROUNDUP(((A1-43)/42)+1,0)

Aladin

============

Posted by Aladin Akyurek on February 14, 2002 8:15 AM

Forgat to

modify also the first bit:

=(A1&LT;=43)*A1+(A1>43)*A1/ROUNDUP(((A1-43)/42)+1,0)

==========

Posted by Aladin Akyurek on February 14, 2002 11:06 AM

I apoligize I misspelled your name. :(

=(A1&LT;=43)*A1+(A1>43)*A1/ROUNDUP(((A1-43)/42)+1,0) ========== :

Posted by Christy on February 15, 2002 4:52 AM

Re: Thank you so much!! This is EXACTLY what I need!!