Multiple formulas within formula

[Alt_Rodell]

Hi All,

I need a single formula which builds multi price tiers according to imputed Price in the selected cell.

Basically a more complex version of a nested formula.

For example, if the cell A1 has a price of :

less than 89, I need the formula to be =sum((A1)1.1)1.7)

less than 150, I need the formula to be =sum((A1)1.1)1.65)

less than 230, I need the formula to be =sum((A1)1.1)1.56)

greater than 230, I need the formula to be =sum((A1)1.1)1.42)

I know it is complex and have looked and looked with no luck...

Any ideas would be much appreciated?

R

[#2]

@Alt_Rodell 

That could be accomplished by an IFS function, with a series of conditions each followed by a formula.

HOWEVER, it is better accomplished by a formula that makes use of a table in which it looks up the variables in the formula. Doing that (a) allows for a simpler formula, (b) makes the variables visible to you, and (c) enables you to change those variables without changing the formula.

I had to make an assumption about what you meant by this formula, =sum((A1)1.1)1.7)

I took it to mean, spelled out in words, multiply whatever is in A1 by 1.1 and add 1.7. If that's not correct, you can change the way my formula works.

[#3]

Thanks Mathetes,

I have not used the table before. This could work.
Unfortunately the formulas are not my forte as I am a novice.

We currently use this formula:
=ROUNDUP((((A1*1.09)*1.1)*1.1)*1.75,0)

This is what I would need to translate into the table so the 1.75 is changeable specifically.

Any thoughts would be much appreciated.

Thank you

[#4]

@Alt_Rodell 

THAT formula is totally different from what you showed before. Totally different.

So although it can still be done via a table, why don't you go back to the layout you showed at the start and make your expectation more complete. Are you now saying you want something like this?

if the cell A1 has a price of :

less than 89, I need the formula to be =ROUNDUP((((A1*1.09)*1.1)*1.1)*1.75,0)

less than 150, I need the formula to be =ROUNDUP((((A1*1.09)*1.1)*1.1)*1.65,0)

less than 230, I need the formula to be =ROUNDUP((((A1*1.09)*1.1)*1.1)*1.56,0)

greater than 230, I need the formula to be =ROUNDUP((((A1*1.09)*1.1)*1.1)*1.42,0)

That series of MULTIPLICATIONS ends up producing prices (assuming that's what they are) that are VERY different from the first formula you requested, where you only multiplied once (by 1.1) and then ADDED the final figure.

I see that you said you're a novice, and that's fine; we all began as novices. But if you mean just to ADD, then you should be using the plus sign; if to multiply, then the asterisk.

It also helps to set thoughts of Excel formulas aside and just describe clearly in words with some basic arithmetic what you are wanting to accomplish.