Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation with IF functions
I have a series of data on production volumes (below). We pay a bonus of min
£50 for upto 500 units of production and then £0.12 per unit above that. I want to calculate the bonus so it is just a single figure in £s in the column next to Prod. Any ideas how to do this? Prod Bonus 275 50 350 50 510 50 + 1.2 = 51.2 600 50 + 12 = 62 Thanks in advance -- daedalus1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation with IF functions
If the unit volume is in A1, then the bonus is =50 + .12 * (max(0,a1-500))
"daedalus1" wrote: I have a series of data on production volumes (below). We pay a bonus of min £50 for upto 500 units of production and then £0.12 per unit above that. I want to calculate the bonus so it is just a single figure in £s in the column next to Prod. Any ideas how to do this? Prod Bonus 275 50 350 50 510 50 + 1.2 = 51.2 600 50 + 12 = 62 Thanks in advance -- daedalus1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation with IF functions
You have been given an elegant answer but if it is homework and you must use
IF =50 + IF(A150, (A1-50)*0.12, 0) Another non-IF formula is =50+ (A150)*(A1-50)*12 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "daedalus1" wrote in message ... I have a series of data on production volumes (below). We pay a bonus of min £50 for upto 500 units of production and then £0.12 per unit above that. I want to calculate the bonus so it is just a single figure in £s in the column next to Prod. Any ideas how to do this? Prod Bonus 275 50 350 50 510 50 + 1.2 = 51.2 600 50 + 12 = 62 Thanks in advance -- daedalus1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation with IF functions
Start a simple worksheet to walk through this example.
In cells A3 to A6 enter your values 275, 350, 510, & 600. In cell E3 enter 500 In cell E4 enter 0.12 In cell E5 enter 50 In cell B3 paste this formula and then copy to cells B4 to B6. =IF(A3<$E$3,$E$5,((A3-$E$3)*$E$4)+$E$5) "daedalus1" wrote: I have a series of data on production volumes (below). We pay a bonus of min £50 for upto 500 units of production and then £0.12 per unit above that. I want to calculate the bonus so it is just a single figure in £s in the column next to Prod. Any ideas how to do this? Prod Bonus 275 50 350 50 510 50 + 1.2 = 51.2 600 50 + 12 = 62 Thanks in advance -- daedalus1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation with IF functions
bpeltzer
Many thanks, it seems to work well except when I checked some of the results with my trusty Casio. For example I have one reading of 586 so it should be 586 - 500 = 86 * 0.12 = 10.32 + 50 = 62.32, it comes up with 62.33, or another one is 703 which should give a result of 74.36, actually gives a result of 74.39 which I cannot explain by looking at the formula. Yet further down the list I have a result of 1041 for the production and the calculated bonus is 114.92 which is correct. A puzzle indeed. -- daedalus1 "bpeltzer" wrote: If the unit volume is in A1, then the bonus is =50 + .12 * (max(0,a1-500)) "daedalus1" wrote: I have a series of data on production volumes (below). We pay a bonus of min £50 for upto 500 units of production and then £0.12 per unit above that. I want to calculate the bonus so it is just a single figure in £s in the column next to Prod. Any ideas how to do this? Prod Bonus 275 50 350 50 510 50 + 1.2 = 51.2 600 50 + 12 = 62 Thanks in advance -- daedalus1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation with IF functions
Thanks all for your help I have sorted out the the issue of the wrong
answers. The data I am using came from someone elses spreadsheet and the data was in whole numbers on the screen but actually had decimals behind it. Basically it was a formatting error on my part. -- daedalus1 "daedalus1" wrote: I have a series of data on production volumes (below). We pay a bonus of min £50 for upto 500 units of production and then £0.12 per unit above that. I want to calculate the bonus so it is just a single figure in £s in the column next to Prod. Any ideas how to do this? Prod Bonus 275 50 350 50 510 50 + 1.2 = 51.2 600 50 + 12 = 62 Thanks in advance -- daedalus1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
Quick date calculation functions | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |