Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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

Check out our Excel Resources

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

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



Re: Using "" 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.


Re: Using "" in the same formula

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 :)


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

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

=(A1<=43)+(A1>43)*ROUNDUP(((A1-43)/42)+1,0)

what you're looking for?

===========



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

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



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

Posted by Christy on February 14, 2002 6:52 AM
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!)



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

Posted by Christy on February 14, 2002 7:30 AM
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!



Kristy: Clarified specs require

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

a simple modification to what we have:

=(A1<=43)+(A1>43)*A1/ROUNDUP(((A1-43)/42)+1,0)

Aladin

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



Forgat to

Posted by Aladin Akyurek on February 14, 2002 8:15 AM
modify also the first bit:

=(A1<=43)*A1+(A1>43)*A1/ROUNDUP(((A1-43)/42)+1,0)

==========



I apoligize I misspelled your name. :(

Posted by Aladin Akyurek on February 14, 2002 11:06 AM
=(A1<=43)*A1+(A1>43)*A1/ROUNDUP(((A1-43)/42)+1,0) ========== :



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

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



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.