| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doesn't it make you mad??
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Doesn't it make you mad??
Sorry! it is 100% not 127%.
Please try again. "Danny Lewis" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Doesn't it make you mad??
Danny Lewis wrote:
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Doesn't it make you mad??
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" wrote: Danny Lewis wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Doesn't it make you mad??
Danny Lewis wrote:
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...). "Franz Verga" wrote: Danny Lewis wrote: 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 -- (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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Doesn't it make you mad??
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. "Danny Lewis" wrote: 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" wrote: Danny Lewis wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Doesn't it make you mad??
Is it just me or does saying 'Advance thanks for rating' make you not want to
do it? "Naveen" wrote: 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. "Danny Lewis" wrote: 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" wrote: Danny Lewis wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Doesn't it make you mad??
Works OK for me ... I always get 100%
(XL2003) "Danny Lewis" wrote: 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" wrote: Danny Lewis wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Doesn't it make you mad??
Thanks chaps it was a needle in a haystack mistake with another formula
"Toppers" wrote: Works OK for me ... I always get 100% (XL2003) "Danny Lewis" wrote: 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" wrote: Danny Lewis wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Doesn't it make you mad??
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% "Danny Lewis" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Make rulers available in excel! | Excel Discussion (Misc queries) | |||
| Can I make formulas more flexible? | Excel Discussion (Misc queries) | |||
| Shuffling Data To Make Lists Match | Excel Worksheet Functions | |||
| make hidden window or workbook visible without specify the name | Excel Worksheet Functions | |||
| make cell contents equal to null value - not blank, but empty | Excel Worksheet Functions | |||
Hybrid Mode
