[SOLVED]If/then range formula help

[rbigrob517]

I am trying to find an easier way to write an IF/And/Then formula that increases on itself by 1. =IF(C2<=C21,B21*1,IF(AND(C2>=(C21+1),C2<=C21*2),B21*2,IF(AND(C2>=(C21*2+1),C2<=C21*3),B21*3)))..... is what i have so far, but I need to do this to at least the 30th power. Is there a way to do this quickly??

[JBeaucaire]

Have a sample workbook showing the actual data you're trying to manipulate? This would be much easier to resolve if we were looking at the same thing you were.

So mockup a sample workbook showing the dilemma and your desired results, point out where the results came from if not obvious in your sample data.

Then click GO ADVANCED and use the paperclip icon to post up your workbook.

[rbigrob517]

Eliot Miracle.xlsx

So this is what i am working on. Cell F21 contains the range formula. I need to be able to see how many rolls of photo paper (cell C21) in need to buy if i sell X amount in cell C2.

[JBeaucaire]

Now explain what the manual math would be in F21 based on all of the current cell values. What would the answer be and why?

What are "roles"? Did you mean "rolls" as in "rolls of paper"?

[DonkeyOte]

Perhaps:

F21: =B21*CEILING(C2/C21,1)

[rbigrob517]

Thanks for the help Ya'll. The Ceiling formula worked great. Still very new to excell and had never seen the Ceilling command until now. Thanks again ya'll.

[DonkeyOte]

Given you're always rounding up to nearest whole number you could use ROUNDUP(C2/C21,0) as opposed to CEILING if preferred (ie if it makes more sense should you come back to review).

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[rbigrob517]

Added feedback and [solved] original post. Thanks again. I will play around with the ceiling and roundup to see how i can best use them in the future.

[JBeaucaire]

In that case, don't forget the reciprocal function FLOOR() which I find I use far more often than CELILING(), though both are irreplaceable. Cheers.