Excel Question?

DavyJones61

I am making a spreadsheet to work out my gas and electricity.


How can I format the cells so that I can put 26.798p in cell A1 and the days in cell A2, say 20, and I get £5.36 and not £535.96 in cell A3 without dividing by 100?

googler

Format column A as Currency £ and enter 0.26798 instead of 26.798?

DavyJones61

Format column A as Currency £ and enter 0.26798 instead of 26.798?

I know that but when copying the prices from websites they are always in the format 26.798 and that is what I want to put in the cell.

bengalknights

Do it the other way put a formula in C as A/100 then column d is C*B

WaywardDriver

Put the required divisor (100) into a convenient blank cell, select and copy.
Select cells with the money values, then Paste Special and set Operation as Divide.

Heedtheadvice

Why would you not want to devide by 100? That is the way to change pence to pounds!
But maybe you just do not want the display to be £500+ and then have to devide in your head?


Just make cells in Row 3 a formula "=A1 * A2 / 100" .

A3 then shows on pounds amd formatting as currency with two decimal points will show as £xx.xx.
Does that meet the need?


Further: it is probaby more usual to do it in one row cells A,B and C rather than in one column rows 1,2 & 3. ; also with suppliers names?- with titles of each column in the top row such as supplier, unit cost, days, total in A1, B1, C1,D1; data in rows below those.



That sort of approach then allows you to filter on the titles (column headings) to select,say, just certain suppliers or those with results less than a target cost or sort them by cost etc.




Edit: or by the suggestions above......though the paste special devide can be more difficult to follow later on!

Paul_DNAP

To do (pence per day) x (days) = (total pounds) you will need to put the divide by 100 in the calculation to convert the pence to pounds, it's not easy to do it with formatting, and you'll only be putting a smoke screen on the display, the number will still be stored as (total pence) and it could cause issues further along if you then took that number into a different report and assumed the number displayed was the number stored.


(pence per day) x (days) ÷ (100 pence per pound) = (total pounds)

molerat

My spreadsheet works with input cells for last read date, new read date and daily charge in pence. = (new read date - old read date) x p charge / 100