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 :-)

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 :-)

=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 :-)

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

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

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

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

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

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