Hi,

I'm after some help on what should be a fairly simple formula...

I have 2 columns of data as follows:

Column A - Product Name
Column B - Quantity

In Column C I would like a formula that does the follwing:

1. Subtracts 1 from the Quantity.
2. Multiplies the revised quantity by a price manually entered in the
formula.
3. Subtracts a % from this value - this % is variable and could be 5%, 10%
or 20%.

So for example:

Product Name = "Apples"
Quantity = 3
% to discount from Sum = 5%
Manual Price = £50

The formula would therefore subtract 1 from 3 (leaving 2), multiply 2 x £50
(price) and then subtract 5% from the total. ie. the cell should provide the
total of £95.

I want to avoid having multiple columns holding the % to remove / the
revised quantity.

Any help would be greatly appreciated!!

Hi,

With a quantity in B2 try this

=(B2-1)*50*(1-5%)


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Doofus" wrote:

Hi,

I'm after some help on what should be a fairly simple formula...

I have 2 columns of data as follows:

Column A - Product Name
Column B - Quantity

In Column C I would like a formula that does the follwing:

1. Subtracts 1 from the Quantity.
2. Multiplies the revised quantity by a price manually entered in the
formula.
3. Subtracts a % from this value - this % is variable and could be 5%, 10%
or 20%.

So for example:

Product Name = "Apples"
Quantity = 3
% to discount from Sum = 5%
Manual Price = £50

The formula would therefore subtract 1 from 3 (leaving 2), multiply 2 x £50
(price) and then subtract 5% from the total. ie. the cell should provide the
total of £95.

I want to avoid having multiple columns holding the % to remove / the
revised quantity.

Any help would be greatly appreciated!!

Mike,

Many thanks, as simple as I expected.... I'd not bracketed the first part of
the formula when I was trying to sort it out.

Really appreciate the help / prompt response.

Rob.

"Mike H" wrote:

Hi,

With a quantity in B2 try this

=(B2-1)*50*(1-5%)


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Doofus" wrote:

Hi,

I'm after some help on what should be a fairly simple formula...

I have 2 columns of data as follows:

Column A - Product Name
Column B - Quantity

In Column C I would like a formula that does the follwing:

1. Subtracts 1 from the Quantity.
2. Multiplies the revised quantity by a price manually entered in the
formula.
3. Subtracts a % from this value - this % is variable and could be 5%, 10%
or 20%.

So for example:

Product Name = "Apples"
Quantity = 3
% to discount from Sum = 5%
Manual Price = £50

The formula would therefore subtract 1 from 3 (leaving 2), multiply 2 x £50
(price) and then subtract 5% from the total. ie. the cell should provide the
total of £95.

I want to avoid having multiple columns holding the % to remove / the
revised quantity.

Any help would be greatly appreciated!!