Formula Help

[John Persico]

I have a column of numbers (prices, i.e. 24.32)

I want to create a formula that does this (I want to create prices with a
markup):

If original number is between 0-4.99, then increase by 100% and go up to
next ??.95,
If original number is between 5-9.99, then increase by 80% and go up to next
??.95,
If original number is between 10-19.99, then increase by 50% and go up to
next ??.95,
etc...

Once I have the basic formula then I can adjust it.

Can anyone help me solve?

Thanks in advance.

 

[Pete_UK]

If you are going to have a few of these, then it would be better to
set up a little table somewhere, eg X1:Y4, like this:

0 100%
5 80%
10 50%
20 30%

Then if your original number is in A1, you could use this formula:

=ROUNDUP(A1*(1+VLOOKUP(A1,X$1:Y$4,2)),0)-0.05

Adjust the table range if you have more than 4 price ranges (note the
range of prices is from 0 to less_than_5, 5 to less_than_10, and so
on, from the first column of the table). Then copy it down as
required.

Hope this helps.

Pete

 

[John Persico]

Are there any conditional formulas in Excel, other than if?


If you are going to have a few of these, then it would be better to
set up a little table somewhere, eg X1:Y4, like this:

0 100%
5 80%
10 50%
20 30%

Then if your original number is in A1, you could use this formula:

=ROUNDUP(A1*(1+VLOOKUP(A1,X$1:Y$4,2)),0)-0.05

Adjust the table range if you have more than 4 price ranges (note the
range of prices is from 0 to less_than_5, 5 to less_than_10, and so
on, from the first column of the table). Then copy it down as
required.

Hope this helps.

Pete

 

[Pete_UK]

If you want to do conditional counts or sums there is COUNTIF and
SUMIF, and in XL2007 and later there is COUNTIFS and SUMIFS for
multiple criteria, but the basic conditional function is IF. You can
have an implied IF (or logical expression), such as:

=B1-A1+(A1>B1)

which will add 1 (equivalent to TRUE) onto the subtraction if A1 is
larger than B1.

In addition, functions like VLOOKUP can be used instead of multiple
IFs.

Why do you ask?

Hope this helps.

Pete

 

[John Persico]

I'm asking due to my first question that you answered (below).
From what you originally stated, if I understand correctly, I have to have
columns of to the side of my main spreadsheet in order to handle my price
markups.
I guess with the VLOOKUP though, I could have that column in another file,
right?
Bascially, I'm trying to avoid having a column that is unrelated to the rest
of my data, that's there only for calcuation purposes. VLOOKUP will allow
me to have it in another file, right?
Then again, I could have another worksheet in the same file, right?


If you want to do conditional counts or sums there is COUNTIF and
SUMIF, and in XL2007 and later there is COUNTIFS and SUMIFS for
multiple criteria, but the basic conditional function is IF. You can
have an implied IF (or logical expression), such as:

=B1-A1+(A1>B1)

which will add 1 (equivalent to TRUE) onto the subtraction if A1 is
larger than B1.

In addition, functions like VLOOKUP can be used instead of multiple
IFs.

Why do you ask?

Hope this helps.

Pete

 

[Pete_UK]

In your first post you quoted 3 IFs followed by etc... I didn't know
how many more you would have, so I gave you the table solution using
VLOOKUP, as that will cope with a large number of variations very
easily - my example showed how you would model 4 variations, but by
extending the table further you could easily cope with 20 or more
rules and just change the range used in the formula. Another advantage
is that if the rules change in the future, it is quite easy to change
the table and the formula remains the same.

You could, of course, produce a formula which uses IFs, but in
versions of Excel before 2007 the number of nested IFs allowed in a
formula was restricted to 7, so this might have been a problem if you
have more.

Yes, you could put the reference table in another sheet (or in another
file). This is quite common practice, and you would need to put the
sheet name in front of the table reference, like so:

=ROUNDUP(A1*(1+VLOOKUP(A1,Sheet2!X$1:Y$4,2)),0)-0.05

If the table is in another file then you would need the full path and
filename in front of the sheet name, but Excel would take longer to re-
calculate the values with that arrangement.

However, if you do only have 4 variations, then you could do away with
the table and incorporate those values within the formula like this:

=ROUNDUP(A1*(1+VLOOKUP(A1,{0,1;5,0.8;10,0.5;20,0.2},2)),0)-0.05

and then copy this down the column. It becomes more difficult to
maintain the sheet in the future, though, if you have the mark-ups
hard-coded within the formula.

Hope this helps.

Pete