Calculation with IF functions

[daedalus1]

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

[bpeltzer]

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

[Bernard Liengme]

You have been given an elegant answer but if it is homework and you must use
IF
=50 + IF(A1>50, (A1-50)*0.12, 0)
Another non-IF formula is =50+ (A1>50)*(A1-50)*12
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"daedalus1" <[email protected]> wrote in message
news:[email protected]...
>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

[Scott Wagner]

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

[daedalus1]

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

[daedalus1]

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