| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to use address function in average function
Hi help needed
I understand how to manipulate indirect & address function to get a value of a cell. However, I would like to get average value of a range instead of the value of a cell. I can use address(....) & ":" & address(...) to produce something like $F$5:$F$10 However, average function does not take $F$5:$F$10 and calculate the average value. Is it possible to do so in excel without writing a VB macro? Thanks Tim |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is it possible to use address function in average function
Entering the following as an array formula works:
=AVERAGE(INDIRECT(ADDRESS(4,2)&":"&ADDRESS(4,6))) To enter an array formula, hit CTRL + SHIFT + ENTER instead of just ENTER. Dave On Dec 17, 11:09 am, wrote: Hi help needed I understand how to manipulate indirect & address function to get a value of a cell. However, I would like to get average value of a range instead of the value of a cell. I can use address(....) & ":" & address(...) to produce something like $F$5:$F$10 However, average function does not take $F$5:$F$10 and calculate the average value. Is it possible to do so in excel without writing a VB macro? Thanks Tim |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is it possible to use address function in average function
With
A1: F5 A2: F10 This formula returns the average of F5:F10 A3: =AVERAGE(INDIRECT(A1&":"&A2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Hi help needed I understand how to manipulate indirect & address function to get a value of a cell. However, I would like to get average value of a range instead of the value of a cell. I can use address(....) & ":" & address(...) to produce something like $F$5:$F$10 However, average function does not take $F$5:$F$10 and calculate the average value. Is it possible to do so in excel without writing a VB macro? Thanks Tim |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is it possible to use address function in average function
I believe, actually, that you don't need an array formula. You can
just enter the formula I give above as a regular formula... Dave On Dec 17, 11:09 am, wrote: Hi help needed I understand how to manipulate indirect & address function to get a value of a cell. However, I would like to get average value of a range instead of the value of a cell. I can use address(....) & ":" & address(...) to produce something like $F$5:$F$10 However, average function does not take $F$5:$F$10 and calculate the average value. Is it possible to do so in excel without writing a VB macro? Thanks Tim |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is it possible to use address function in average function
With these values in:
C1 = 5 C2 = 6 C3 = 10 Try this: =AVERAGE(INDIRECT(ADDRESS(C1,C2)):INDIRECT(ADDRESS (C3,C2))) OR this: =AVERAGE(INDIRECT(ADDRESS(C1,C2)&":"&ADDRESS(C3,C2 ))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ... Hi help needed I understand how to manipulate indirect & address function to get a value of a cell. However, I would like to get average value of a range instead of the value of a cell. I can use address(....) & ":" & address(...) to produce something like $F$5:$F$10 However, average function does not take $F$5:$F$10 and calculate the average value. Is it possible to do so in excel without writing a VB macro? Thanks Tim |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is it possible to use address function in average function
Ron,
It does not work. I got #REF here is the exact code I use: ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0), 3,1,TRUE,"Sheet2")&":"&ADDRESS(MATCH($A2,Sheet2!$A $2:$A $2000,0)-19,3,1,TRUE,"Sheet2") above code gives me : Sheet2!$C$65:Sheet2!$C$46 However, average(indirect(above code)) give me #REF any idea? On Dec 17, 11:14 am, "Ron Coderre" wrote: With A1: F5 A2: F10 This formula returns the average of F5:F10 A3: =AVERAGE(INDIRECT(A1&":"&A2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Hi help needed I understand how to manipulate indirect & address function to get a value of a cell. However, I would like to get average value of a range instead of the value of a cell. I can use address(....) & ":" & address(...) to produce something like $F$5:$F$10 However, average function does not take $F$5:$F$10 and calculate the average value. Is it possible to do so in excel without writing a VB macro? Thanks Tim |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is it possible to use address function in average function
You can't use the sheet reference in both sections of the formula.
Try this: =SUM(INDIRECT( ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0),3,1,TRUE, "Sheet2")&":"& ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0)-19,3,1,TRUE))) A sample of the interal section would be: "Sheet2!$C$65:$C$46" Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Ron, It does not work. I got #REF here is the exact code I use: ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0), 3,1,TRUE,"Sheet2")&":"&ADDRESS(MATCH($A2,Sheet2!$A $2:$A $2000,0)-19,3,1,TRUE,"Sheet2") above code gives me : Sheet2!$C$65:Sheet2!$C$46 However, average(indirect(above code)) give me #REF any idea? On Dec 17, 11:14 am, "Ron Coderre" wrote: With A1: F5 A2: F10 This formula returns the average of F5:F10 A3: =AVERAGE(INDIRECT(A1&":"&A2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Hi help needed I understand how to manipulate indirect & address function to get a value of a cell. However, I would like to get average value of a range instead of the value of a cell. I can use address(....) & ":" & address(...) to produce something like $F$5:$F$10 However, average function does not take $F$5:$F$10 and calculate the average value. Is it possible to do so in excel without writing a VB macro? Thanks Tim |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is it possible to use address function in average function
Typo......"SUM" should be "AVERAGE":
=AVERAGE(INDIRECT( ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0),3,1,TRUE, "Sheet2")&":"& ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0)-19,3,1,TRUE))) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... You can't use the sheet reference in both sections of the formula. Try this: =SUM(INDIRECT( ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0),3,1,TRUE, "Sheet2")&":"& ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0)-19,3,1,TRUE))) A sample of the interal section would be: "Sheet2!$C$65:$C$46" Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Ron, It does not work. I got #REF here is the exact code I use: ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0), 3,1,TRUE,"Sheet2")&":"&ADDRESS(MATCH($A2,Sheet2!$A $2:$A $2000,0)-19,3,1,TRUE,"Sheet2") above code gives me : Sheet2!$C$65:Sheet2!$C$46 However, average(indirect(above code)) give me #REF any idea? On Dec 17, 11:14 am, "Ron Coderre" wrote: With A1: F5 A2: F10 This formula returns the average of F5:F10 A3: =AVERAGE(INDIRECT(A1&":"&A2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Hi help needed I understand how to manipulate indirect & address function to get a value of a cell. However, I would like to get average value of a range instead of the value of a cell. I can use address(....) & ":" & address(...) to produce something like $F$5:$F$10 However, average function does not take $F$5:$F$10 and calculate the average value. Is it possible to do so in excel without writing a VB macro? Thanks Tim |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is it possible to use address function in average function
Give this a try:
=AVERAGE(INDEX(Sheet2!C:C,MATCH($A2,Sheet2!$A$2:$A $2000,0)):INDEX(Sheet2!C:C,MATCH($A2,Sheet2!$A$2:$ A$2000,0)-19)) *However*, I'm confused by the way you're referencing your ranges. Your match range starts at A2. What happens if Match() finds a match in say, A10? That makes the *ending* cell of the Average range to be C9. SO, you then subtract *19 rows* from C9 to reference your *starting* cell!?!? Since that doesn't exist (C-10), you'll get a #Value! error. It sounds to me that perhaps you have to re-figure your logic. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... Ron, It does not work. I got #REF here is the exact code I use: ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0), 3,1,TRUE,"Sheet2")&":"&ADDRESS(MATCH($A2,Sheet2!$A $2:$A $2000,0)-19,3,1,TRUE,"Sheet2") above code gives me : Sheet2!$C$65:Sheet2!$C$46 However, average(indirect(above code)) give me #REF any idea? On Dec 17, 11:14 am, "Ron Coderre" wrote: With A1: F5 A2: F10 This formula returns the average of F5:F10 A3: =AVERAGE(INDIRECT(A1&":"&A2)) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Hi help needed I understand how to manipulate indirect & address function to get a value of a cell. However, I would like to get average value of a range instead of the value of a cell. I can use address(....) & ":" & address(...) to produce something like $F$5:$F$10 However, average function does not take $F$5:$F$10 and calculate the average value. Is it possible to do so in excel without writing a VB macro? Thanks Tim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| cannot nest address function into another function | Excel Discussion (Misc queries) | |||
| Average Function and dynamic cell address | Excel Worksheet Functions | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
| How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Linear Mode
