maths or Excel help please???

alfablue

Any maths and / or Excel whizz kids here? I am afraid I am embarrassingly poor at maths!!!

Firstly, I have made a spreadsheet to calculate the percentage gross profit (GP) from menu items as a percentage (selling price less ingredients cost). So far so good, but...

Is it possible to calculate the necessary selling price to achieve a given GP and a known ingredient cost? If so what formula do I need?

e.g.

Beer battered Pollack, triple cooked chips and mushy peas, ingredients cost £2.07, sells at £10.25, gives a GP of 79.8% - I want to be able to determine what selling price is required to achieve a GP of 75%.

Secondly, in Excel, is it possible to round the value in a cell that already has a formula? I can only do this across 2 cells (i.e. formula in one cell, adjacent cell has ROUND formula).

Many thanks in anticipation

neil²

For EXCEL you can cascade formulae by putting them in brackets....
e.g. =ROUND((A6+A7),)

For the required GP, you need
cost / (1-GP)

which is
cost / 0.25

for 75% (=0.75) GP

alfablue

Neil, that is fantastic help on the percentage, thank you (doh, its embarrassingly simple!).

I can't quite get the round thing to work. I need to enter Round(number, number of digits). I want it to round up or down to the next integer so I need Round(number,0) I think. I am trying to round the following:

=ABS(C2/B2) [ to return the percentage C2 is of B2]

but having tried various ways I can't get it to work.

I think I should have =ROUND((C2/B2),0) but it returns 100%

hmmmmm...

Sorry to ask for more help, but I would be very grateful if you could point me in the right direction 8)

neil²

alfablue wrote:

Neil, that is fantastic help on the percentage, thank you (doh, its embarrassingly simple!).

I can't quite get the round thing to work. I need to enter Round(number, number of digits). I want it to round up or down to the next integer so I need Round(number,0) I think. I am trying to round the following:

=ABS(C2/B2) [ to return the percentage C2 is of B2]

but having tried various ways I can't get it to work.

I think I should have =ROUND((C2/B2),0) but it returns 100%

hmmmmm...

Sorry to ask for more help, but I would be very grateful if you could point me in the right direction 8)

The 0 in

=ROUND((C2/B2),0)

is the number of digits precision. If you want 2 digits, replace it with a 2.

alfablue

Ah, superb, thank you!!!!!!!!!

neil²

...more...

if you need an integer then you will need to multiply your % by 100, and display as decimal. Then you can use INT which always rounds down. If you want the nearest, add 0.5 before the INT. I don't think that ABS is what you need (it just makes it positive).

alfablue

Thanks again Neil,

yes, though if I now use round I don't need ABS anyway. I have set the cell format to show percentage so it seems to achieve what I need now. I had thought the 0 in the Round formula indicated decimal places, not number of digits).

so I have ROUND((C2/B2),0) and the cell as percent with 0 decimal places and it seems to report a whole % rounded up or down appropriately.