| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cost Charge Conundrum
I've got to calculate whether some total charges are correct but the format
of the charges is not straight-forward. It is based on a range ie. 1 = $100 2-10 = $75 11 - 20 = $50 21+ = $25 These charges are applied in steps. ie. if there is 11, the 1st will be charged $100, the 2nd - 10th charged $75 each and the 11th charged $50. What function can I use to indicate the total charges are correct with "OK" "NOT OK"? I am using a reference figure for the total number of products and another reference figure for the total charges - to compare against. Any one got any ideas?????? TIA |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cost Charge Conundrum
=MAX(0,MIN(A1,1))*100+MAX(0,MIN(A1-1,9))*75+MAX(0,MIN(A1-10,10))*50+MAX(0,A1-20)*25
-- __________________________________ HTH Bob "kjh198" wrote in message ... I've got to calculate whether some total charges are correct but the format of the charges is not straight-forward. It is based on a range ie. 1 = $100 2-10 = $75 11 - 20 = $50 21+ = $25 These charges are applied in steps. ie. if there is 11, the 1st will be charged $100, the 2nd - 10th charged $75 each and the 11th charged $50. What function can I use to indicate the total charges are correct with "OK" "NOT OK"? I am using a reference figure for the total number of products and another reference figure for the total charges - to compare against. Any one got any ideas?????? TIA |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cost Charge Conundrum
Bob, you're a star that's perfect! ;)
"Bob Phillips" wrote: =MAX(0,MIN(A1,1))*100+MAX(0,MIN(A1-1,9))*75+MAX(0,MIN(A1-10,10))*50+MAX(0,A1-20)*25 -- __________________________________ HTH Bob "kjh198" wrote in message ... I've got to calculate whether some total charges are correct but the format of the charges is not straight-forward. It is based on a range ie. 1 = $100 2-10 = $75 11 - 20 = $50 21+ = $25 These charges are applied in steps. ie. if there is 11, the 1st will be charged $100, the 2nd - 10th charged $75 each and the 11th charged $50. What function can I use to indicate the total charges are correct with "OK" "NOT OK"? I am using a reference figure for the total number of products and another reference figure for the total charges - to compare against. Any one got any ideas?????? TIA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conundrum | Excel Discussion (Misc queries) | |||
| COUNTIF conundrum | Excel Worksheet Functions | |||
| VBA conundrum | New Users to Excel | |||
| Current Cost versus Original Cost | New Users to Excel | |||
| Confusion on adding percentage of cost to that cost. | Excel Worksheet Functions | |||
Linear Mode
