| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i make a formula with varied discount percentages
First let me say Hello as this is my first time here :)
I am trying to make a column that as a varied percent discount. By this I mean I want to make it so it deducts 5.25% from items sold with a value of £0.01 - £29.99 But if the item sold is higher than £29.99 then it needs to Deduct 5.25% for the first £29.99 then to deduct 3.25% for the rest. Hope this makes sense. Thanks in advance Steve |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i make a formula with varied discount percentages
Assuming that A1 contains the value of the item sold, try...
=SUMPRODUCT(--(A1{0,29.99}),A1-{0,29.99},{0.0525,-0.02}) Hope this helps! In article . com, "ouch" wrote: First let me say Hello as this is my first time here :) I am trying to make a column that as a varied percent discount. By this I mean I want to make it so it deducts 5.25% from items sold with a value of £0.01 - £29.99 But if the item sold is higher than £29.99 then it needs to Deduct 5.25% for the first £29.99 then to deduct 3.25% for the rest. Hope this makes sense. Thanks in advance Steve |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i make a formula with varied discount percentages
"Domenic" wrote in message
... In article . com, "ouch" wrote: First let me say Hello as this is my first time here :) I am trying to make a column that as a varied percent discount. By this I mean I want to make it so it deducts 5.25% from items sold with a value of £0.01 - £29.99 But if the item sold is higher than £29.99 then it needs to Deduct 5.25% for the first £29.99 then to deduct 3.25% for the rest. Hope this makes sense. Thanks in advance Steve Assuming that A1 contains the value of the item sold, try... =SUMPRODUCT(--(A1{0,29.99}),A1-{0,29.99},{0.0525,-0.02}) or =A1-5.25%*MIN(A1,29.99)-3.25%*MAX(0,A1-29.99) or =A1*(1-5.25%)+2%*MAX(0,A1-29.99) -- David Biddulph |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how do i make a formula with varied discount percentages
Thanks guys all sorted for now :)
David Biddulph wrote: "Domenic" wrote in message ... In article . com, "ouch" wrote: First let me say Hello as this is my first time here :) I am trying to make a column that as a varied percent discount. By this I mean I want to make it so it deducts 5.25% from items sold with a value of £0.01 - £29.99 But if the item sold is higher than £29.99 then it needs to Deduct 5.25% for the first £29.99 then to deduct 3.25% for the rest. Hope this makes sense. Thanks in advance Steve Assuming that A1 contains the value of the item sold, try... =SUMPRODUCT(--(A1{0,29.99}),A1-{0,29.99},{0.0525,-0.02}) or =A1-5.25%*MIN(A1,29.99)-3.25%*MAX(0,A1-29.99) or =A1*(1-5.25%)+2%*MAX(0,A1-29.99) -- David Biddulph |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible? | Excel Worksheet Functions | |||
| trying to make a formula for percent if greater than a value | Excel Discussion (Misc queries) | |||
| I wish to make a formula that adds dollars in 1 column if a date . | Excel Worksheet Functions | |||
| Can I make a formula in Excel to display result in same cell? | Excel Worksheet Functions | |||
| how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Linear Mode
