MAXIMUM VALUE PROBLEM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)


Whiffer said:
I need to know how to reduce a figure by a percentage, up to a maximum
Click to expand...
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.
 
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





Whiffer said:
I need to know how to reduce a figure by a percentage, up to a maximum
Click to expand...
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.
 
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




Whiffer said:
Thanks for these. My next problem is to how to use these suggestions in
Click to expand...
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.
Click to expand...
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.
 
Back
Top