Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM returns #VALUE! error
In B6 the formula
=SUM(ADDRESS(B2,COLUMN(B6))&":"&ADDRESS(B3,COLUMN( B6))) returns a #VALUE! error. The formula =SUM(INDIRECT(B5)) returns the correct answer, when B5 contains =ADDRESS(B2,COLUMN(B6))&":"&ADDRESS(B3,COLUMN(B6)) and returns the correct absolute address $B$9:$B$25, a range with some numbers. B2&B3 values may vary and the sum fomula is to be filled across row 6. Am I missing something obvious? Kevin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM returns #VALUE! error
Hi
You need to wrap it in an indirect function itself for Sum to return the answer. =SUM(INDIRECT((ADDRESS(B2,COLUMN(B6))&":"&ADDRESS( B3,COLUMN(B6))))) But you would have fewer calls it you did it directly (well indirectly really<bg) with =SUM(INDIRECT("B"&B2&":B"&B3)) note the colon before the second B -- Regards Roger Govier "kjs" wrote in message ... In B6 the formula =SUM(ADDRESS(B2,COLUMN(B6))&":"&ADDRESS(B3,COLUMN( B6))) returns a #VALUE! error. The formula =SUM(INDIRECT(B5)) returns the correct answer, when B5 contains =ADDRESS(B2,COLUMN(B6))&":"&ADDRESS(B3,COLUMN(B6)) and returns the correct absolute address $B$9:$B$25, a range with some numbers. B2&B3 values may vary and the sum fomula is to be filled across row 6. Am I missing something obvious? Kevin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM returns #VALUE! error
Hi
You need to wrap it in an indirect function itself for Sum to return the answer. =SUM(INDIRECT((ADDRESS(B2,COLUMN(B6))&":"&ADDRESS( B3,COLUMN(B6))))) But you would have fewer calls it you did it directly (well indirectly really<bg) with =SUM(INDIRECT("B"&B2&":B"&B3)) note the colon before the second B -- Regards Roger Govier Thanks for the fix Roger. I'll be using the top one as I need to fill across columns. Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
VLOOKUP using a cell calculated with NOW returns Error | Excel Worksheet Functions | |||
error opening excel 2000 files | Excel Discussion (Misc queries) | |||
ERROR | Excel Discussion (Misc queries) | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel |