Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Very simple percentage problem
Hi
I'm new to spreadsheets and I suspect this problem is very easy to solve, G2 is a value of £1048.00. In H2 I want to show this as a percentage gain from a starting point of £1000.00 ie 4.8%. I would like H2 to show 4.8% or 4.80%. I'm going round in circles..... Many thanks :-) |
#2
|
|||
|
|||
With 1000 in B1, enter this formula in H2:
=(A1-B1)/B1 or, without B1, (A1-1000)/1000 It will show 0.048. Now FormatCellNumber, choose Percentage with 2 decimals. -- Kind regards, Niek Otten Microsoft MVP - Excel "News Account" wrote in message ... Hi I'm new to spreadsheets and I suspect this problem is very easy to solve, G2 is a value of £1048.00. In H2 I want to show this as a percentage gain from a starting point of £1000.00 ie 4.8%. I would like H2 to show 4.8% or 4.80%. I'm going round in circles..... Many thanks :-) |
#3
|
|||
|
|||
=MOD(G2,1000)/1000
and format as percentage -- HTH Bob Phillips "News Account" wrote in message ... Hi I'm new to spreadsheets and I suspect this problem is very easy to solve, G2 is a value of £1048.00. In H2 I want to show this as a percentage gain from a starting point of £1000.00 ie 4.8%. I would like H2 to show 4.8% or 4.80%. I'm going round in circles..... Many thanks :-) |
#4
|
|||
|
|||
Hi
I'm new to spreadsheets and I suspect this problem is very easy to solve, G2 is a value of £1048.00. In H2 I want to show this as a percentage gain from a starting point of £1000.00 ie 4.8%. I would like H2 to show 4.8% or 4.80%. I'm going round in circles..... Many thanks :-) =MOD(G2,1000)/1000 and format as percentage HTH Bob Phillips Wow, fantastic, I'd never have known that! Is there anyway of modiying the formula to deal with 952 as a percentage of a 1000? In the sense that for the purposes of my spreadsheet this is a negative %... -4.80% Thanks |
#5
|
|||
|
|||
My formula works for negatives as well.
-- Kind regards, Niek Otten Microsoft MVP - Excel "News Account" wrote in message ... Hi I'm new to spreadsheets and I suspect this problem is very easy to solve, G2 is a value of £1048.00. In H2 I want to show this as a percentage gain from a starting point of £1000.00 ie 4.8%. I would like H2 to show 4.8% or 4.80%. I'm going round in circles..... Many thanks :-) =MOD(G2,1000)/1000 and format as percentage HTH Bob Phillips Wow, fantastic, I'd never have known that! Is there anyway of modiying the formula to deal with 952 as a percentage of a 1000? In the sense that for the purposes of my spreadsheet this is a negative %... -4.80% Thanks |
#6
|
|||
|
|||
It can be done
=IF(G3<1000,-MOD(1000,G3),MOD(G3,1000))/1000 If you don't need the negative sign you could use =MOD(MAX(1000,G4),MIN(1000,G4))/1000 but in these circumstances, Nick's formula looks simpler. -- HTH Bob Phillips "News Account" wrote in message ... Hi I'm new to spreadsheets and I suspect this problem is very easy to solve, G2 is a value of £1048.00. In H2 I want to show this as a percentage gain from a starting point of £1000.00 ie 4.8%. I would like H2 to show 4.8% or 4.80%. I'm going round in circles..... Many thanks :-) =MOD(G2,1000)/1000 and format as percentage HTH Bob Phillips Wow, fantastic, I'd never have known that! Is there anyway of modiying the formula to deal with 952 as a percentage of a 1000? In the sense that for the purposes of my spreadsheet this is a negative %... -4.80% Thanks |
#7
|
|||
|
|||
Apologies, I meant Niek, not Nick.
-- HTH Bob Phillips "Bob Phillips" wrote in message ... It can be done =IF(G3<1000,-MOD(1000,G3),MOD(G3,1000))/1000 If you don't need the negative sign you could use =MOD(MAX(1000,G4),MIN(1000,G4))/1000 but in these circumstances, Nick's formula looks simpler. -- HTH Bob Phillips "News Account" wrote in message ... Hi I'm new to spreadsheets and I suspect this problem is very easy to solve, G2 is a value of £1048.00. In H2 I want to show this as a percentage gain from a starting point of £1000.00 ie 4.8%. I would like H2 to show 4.8% or 4.80%. I'm going round in circles..... Many thanks :-) =MOD(G2,1000)/1000 and format as percentage HTH Bob Phillips Wow, fantastic, I'd never have known that! Is there anyway of modiying the formula to deal with 952 as a percentage of a 1000? In the sense that for the purposes of my spreadsheet this is a negative %... -4.80% Thanks |
#8
|
|||
|
|||
Hi Bob,
In all those years in newsgroups, I just had to get used to it! Thanks anyway -- Kind regards, Niek Otten Microsoft MVP - Excel "Bob Phillips" wrote in message ... Apologies, I meant Niek, not Nick. -- HTH Bob Phillips "Bob Phillips" wrote in message ... It can be done =IF(G3<1000,-MOD(1000,G3),MOD(G3,1000))/1000 If you don't need the negative sign you could use =MOD(MAX(1000,G4),MIN(1000,G4))/1000 but in these circumstances, Nick's formula looks simpler. -- HTH Bob Phillips "News Account" wrote in message ... Hi I'm new to spreadsheets and I suspect this problem is very easy to solve, G2 is a value of £1048.00. In H2 I want to show this as a percentage gain from a starting point of £1000.00 ie 4.8%. I would like H2 to show 4.8% or 4.80%. I'm going round in circles..... Many thanks :-) =MOD(G2,1000)/1000 and format as percentage HTH Bob Phillips Wow, fantastic, I'd never have known that! Is there anyway of modiying the formula to deal with 952 as a percentage of a 1000? In the sense that for the purposes of my spreadsheet this is a negative %... -4.80% Thanks |
#9
|
|||
|
|||
Hi Niek,
It was a finger-blur, not a brain-blur. I try to be very careful about names, as I often get my surname spelt with just one l, and it annoys me so much that I try and not be guilty of like errors. Anyway, even if you are used to it, it still deserves an apology :-) Regards Bob "Niek Otten" wrote in message ... Hi Bob, In all those years in newsgroups, I just had to get used to it! Thanks anyway -- Kind regards, Niek Otten Microsoft MVP - Excel "Bob Phillips" wrote in message ... Apologies, I meant Niek, not Nick. -- HTH Bob Phillips "Bob Phillips" wrote in message ... It can be done =IF(G3<1000,-MOD(1000,G3),MOD(G3,1000))/1000 If you don't need the negative sign you could use =MOD(MAX(1000,G4),MIN(1000,G4))/1000 but in these circumstances, Nick's formula looks simpler. -- HTH Bob Phillips "News Account" wrote in message ... Hi I'm new to spreadsheets and I suspect this problem is very easy to solve, G2 is a value of £1048.00. In H2 I want to show this as a percentage gain from a starting point of £1000.00 ie 4.8%. I would like H2 to show 4.8% or 4.80%. I'm going round in circles..... Many thanks :-) =MOD(G2,1000)/1000 and format as percentage HTH Bob Phillips Wow, fantastic, I'd never have known that! Is there anyway of modiying the formula to deal with 952 as a percentage of a 1000? In the sense that for the purposes of my spreadsheet this is a negative %... -4.80% Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i've got a simple excel problem that needs solving... | Excel Discussion (Misc queries) | |||
Simple problem | Excel Discussion (Misc queries) | |||
Percentage problem | Excel Discussion (Misc queries) | |||
Simple Rounding Problem | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) |