Re: Limit a sum using discounts

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Duncan, UK (DuncanUK_at_discussions.microsoft.com)
Date: 01/20/05 

Date: Thu, 20 Jan 2005 09:07:03 -0800

At the moment the sum looks for G24 where the discount can be set eg 40%, but
I was thinking of using a hidden cell in certain rows where a product can
only have a limited discount eg h56 to say =MIN(SUM(g24),35), then my sum to
calculate the total price (value x quantity - discount) would like
=(E9*F9)*(1-$h$56). This works if G24 & H56 are formatted as numbers, but
not as percentages.

Thanks.

"Bernard Liengme" wrote:

> If row 9 represents a product, what in this row tells us it is a limited
> discount item?
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Duncan, UK" <DuncanUK@discussions.microsoft.com> wrote in message
> news:5E9C494A-C66A-46C1-ABFC-C3655E1A5661@microsoft.com...
> >I am trying to cap a cell that I use for discounts.
> > eg the cell could read 35%, but I only want a max of 30% on some products
> > I am using a cell to input a discount into quotations, this cell is then
> > used to calculate sums further below eg
> > g24 = 40%
> > and I use =(E9*F9)*(1-$G$24) later to get my total BUT I want to be able
> > to
> > cap some lines with a lower discount eg some products can have up to 40%
> > ,but
> > others can only go up to 30%.
> > =MIN(SUM(A1:A10),100) This sum works if the cell is formatted as a
> > general
> > number, but not if it is formatted as a percentage.
> >
> > Help please...
>
>
>

Relevant Pages

  • Re: How do I add iserr or iserror to this
    ... If you have no data in Discount or Price, ... If you blank out a value in Discount or Price using the space bar, ... Provided you have NUMERIC values or a genuine blank cell in Price and Discount, you will ALWAYS get an answer in Result. ...
    (microsoft.public.excel.programming)
  • Re: Limit a sum using discounts
    ... Don't forget, if the cell is formatted as a percentage, it should only ... > At the moment the sum looks for G24 where the discount can be set eg 40%, ... >>>I am trying to cap a cell that I use for discounts. ...
    (microsoft.public.excel.misc)
  • Re: Calculating on alphabetic cell content
    ... > Discount A=40% ... with the desired calculations and results. ... >>> Specified problem description. ... >>> prize next to the original cell holding the letter. ...
    (microsoft.public.excel.newusers)
  • Re: How do I add iserr or iserror to this
    ... I assigned the name "Disc" to a cell formatted as percentage. ... Again basically if there is no discount to be given the cell should just be populated with what is in M29, if there is a discount to be given then is will do the math to show the discounted amount in the cell. ... Again this all works great but when there is no part number to price against or give a discount against I get the #Value error in the field because the answer is 0, which messes up price sum value. ... >>>> I guess I should explain, I have a set of cells that show full price M29 - ...
    (microsoft.public.excel.programming)
  • Re: How do I add iserr or iserror to this
    ... Thanks Art you were right, my math for disc was wrong and it was throwing ... I assigned the name "Disc" to a cell formatted as ... Again basically if there is no discount to be given the cell should just be ... which messes up price sum value. ...
    (microsoft.public.excel.programming)