Back to Forms in Excel VBA archive index

Back to archive home

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

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

You could try this :

=IF(B5>85,"",IF(B5>43,"DO THIS","DO THAT"))

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

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

Is

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

what you're looking for?

===========

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

Thank you so much!! Thanks also to Phil!

(sorry about the previous entry, I hit "enter too many times after my name!)

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!

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

a simple modification to what we have:

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

Aladin

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

modify also the first bit:

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

==========

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

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.

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.