MAXIMUM VALUE PROBLEM

[Whiffer]

I need to know how to reduce a figure by a percentage, up to a maximum figure(if this makes sense!). E.g. I have a figure in a cell of 3000, I enter a figure in another cell, say 100, and I went to reduce the 3000 by 5%, but up to a maximum of the figure in the second cell, i.e. 100. So, if the figure in my first cell is just 1000 and I enter 100 in the second cell, it will return me a figure of 950. Any help would be greatly appreciated. Thanks.

 

[Peo Sjoblom]

With the figure you want to reduce in A2, the [but not more than this
figure] in B2 this formula will do what you want

=A2-MIN(A2*5%,$B$2)

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

I need to know how to reduce a figure by a percentage, up to a maximum

figure(if this makes sense!). E.g. I have a figure in a cell of 3000, I
enter a figure in another cell, say 100, and I went to reduce the 3000 by
5%, but up to a maximum of the figure in the second cell, i.e. 100. So, if
the figure in my first cell is just 1000 and I enter 100 in the second cell,
it will return me a figure of 950. Any help would be greatly appreciated.
Thanks.

 

[CLR]

Hi Whiffer.........

Try this, I think it's what you're after.......

A1 put your 1000
B1 put your 100
C1 put =A1*.05
D1 put =A1-(MIN(B1,C1))

D1 will display 950 because C1 is less than B1..........
You can copy C1 ad D1 down as far as you have data in A1 and B1

Vaya con Dios,
Chuck, CABGx3

I need to know how to reduce a figure by a percentage, up to a maximum

figure(if this makes sense!). E.g. I have a figure in a cell of 3000, I
enter a figure in another cell, say 100, and I went to reduce the 3000 by
5%, but up to a maximum of the figure in the second cell, i.e. 100. So, if
the figure in my first cell is just 1000 and I enter 100 in the second cell,
it will return me a figure of 950. Any help would be greatly appreciated.
Thanks.

 

[Harry Bo]

Whiffer,

a1 =3000 b1 = say 100 in c1 use formula:
=IF(A1*5%>B1,B1,A1-(A1*5%))

Harry

 

[CLR]

Hi Whiffer.

Maybe this variation of Harry Bo's suggestion to include your new cell
numbers............just put it in the cell where you want the result.

=IF(H23*VLOOKUP($C33,'Chaucer
Discounts'!$D61:$E65,2,FALSE)>C33,C33,H23-(H23*VLOOKUP($C33,'Chaucer
Discounts'!$D61:$E65,2,FALSE))

Vaya con Dios,
Chuck, CABGx3

Thanks for these. My next problem is to how to use these suggestions in

the spread sheet. Please can you suggest a formula where A1 is H23, B1 is
C33, and C1 is a variable, which is in a table refered to by
*VLOOKUP($C33,'Chaucer Discounts'!$D61:$E65,2).

Thanks again.

figure(if this makes sense!). E.g. I have a figure in a cell of 3000, I
enter a figure in another cell, say 100, and I went to reduce the 3000 by
5%, but up to a maximum of the figure in the second cell, i.e. 100. So, if
the figure in my first cell is just 1000 and I enter 100 in the second cell,
it will return me a figure of 950. Any help would be greatly appreciated.
Thanks.