Doesn't it make you mad??

[Guest]

You sit on Excel for hours, days on end, working, and every now and again you
do the SIMPLEST thing and it's not working.

It's a wonder my monitor is still in the building and not out the window...

I will simplify my problem for ease of reading

In column A are the letters A-Z from A3:A28
In column B are random numbers from B3:B28
In B30 is the total of those random numbers.

In C3:C28 is the numbers in B3:B28 expressed as a percentage of the total
(B30)

ie say B12 is 20, and the total is 100, C12 would read 20%.

The formula in each of the cells in column C is
=IF(ISERROR($B3/$B$30),"",$B3/$B$30) and so on, this is run from row 3 to 28.

Here's the funny bit: the numbers in column C add up to 127%.

Any clues? lol

 

[Franz Verga]

You sit on Excel for hours, days on end, working, and every now and
again you do the SIMPLEST thing and it's not working.

It's a wonder my monitor is still in the building and not out the
window...

I will simplify my problem for ease of reading

In column A are the letters A-Z from A3:A28
In column B are random numbers from B3:B28
In B30 is the total of those random numbers.

In C3:C28 is the numbers in B3:B28 expressed as a percentage of the
total (B30)

ie say B12 is 20, and the total is 100, C12 would read 20%.

The formula in each of the cells in column C is
=IF(ISERROR($B3/$B$30),"",$B3/$B$30) and so on, this is run from row
3 to 28.

Here's the funny bit: the numbers in column C add up to 127%.

Hi Danny,

maybe it depends on *how* do you generate the random numbers...

I tried on my Excel 2003 as you described and it works. To generate the
random numbers I used the RAND() function.



--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Sorry! it is 100% not 127%.

Please try again.

 

[Guest]

Nafeez I know it should be 100%! That's the problem lol!

Sorry the random part was to simplify Franz, they are actual figures. The
figure in B30 is the total of B3 to B28, therefore the figures in C3 to C28
should total 100% right???

 

[Franz Verga]

Nafeez I know it should be 100%! That's the problem lol!

Sorry the random part was to simplify Franz, they are actual figures.
Ok

The figure in B30 is the total of B3 to B28, therefore the figures in
C3 to C28 should total 100% right???

Not just should, they *must* total 100%... Maybe it depends in some
settings...

Try this: menu Tools, Options, Calculation, in the section Workbook Options
Uncheck "Approximation as visalized" (or something like this...).


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Dear Danny Lewis,

I tried it, it is 100% only not 127%.

Check for totals in B30 and also C30,
also check whether there exists any value in B29 or in C29.

I have tried it and it is 100% only.

Advance thanks for rating.

 

[Guest]

Works OK for me ... I always get 100%

(XL2003)

Nafeez I know it should be 100%! That's the problem lol!

Sorry the random part was to simplify Franz, they are actual figures. The
figure in B30 is the total of B3 to B28, therefore the figures in C3 to C28
should total 100% right???

 

[Guest]

Dear Danny Lewis,

Check for formula in B30 and C30
Delete any figures in B29 and C29, if exists

Re-run, you will get 100%
not 127%

 

[Guest]

Thanks chaps it was a needle in a haystack mistake with another formula

 

[Guest]

Is it just me or does saying 'Advance thanks for rating' make you not want to
do it?