| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to: have something equal Cx where x=8+ C4
Hi,
I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to: have something equal Cx where x=8+ C4
Try: =AVERAGE(INDIRECT("C5:C"&A1))
where A1 will house the row number -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Raget" wrote: Hi, I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to: have something equal Cx where x=8+ C4
Another one:
=AVERAGE(C5:INDEX(C:C,A1)) A1 = 10 The formula will average C5:C10 If A1 is empty the entire range will be calculated. -- Biff Microsoft Excel MVP "Raget" wrote in message ... Hi, I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to: have something equal Cx where x=8+ C4
Hi,
Yeh, that worked kind of, but i cant switch the two around. My original post was incorrect. I wish to have a series of numbers, and to have next to each number, the average over the past x ammount of numbers, but i dont want to have to change the cell and fill it everytime. So it would be something like (to use my old and non-working example) =average(C(20-A1):C20). and i would want the A1 to be the number of values it takes into account for the average, eg, if i were to want 5 numbers A1= 5, thus =average(C15:C20). I would also want the 20 to automatically change, so that in cell C50, it would be =average(C(50-A1):C50). I hope i am making sense, sorry for being difficult. Thanks "Max" wrote: Try: =AVERAGE(INDIRECT("C5:C"&A1)) where A1 will house the row number -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Raget" wrote: Hi, I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to: have something equal Cx where x=8+ C4
=AVERAGE(C5:INDEX(C:C,A1)) doesnt quite give me what i need.
is it giving the average from c5 to c(5+A1)? when i edit it, the entire C column is included? What am i missing here? "T. Valko" wrote: Another one: =AVERAGE(C5:INDEX(C:C,A1)) A1 = 10 The formula will average C5:C10 If A1 is empty the entire range will be calculated. -- Biff Microsoft Excel MVP "Raget" wrote in message ... Hi, I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to: have something equal Cx where x=8+ C4
I read your reply to Max but I don't understand what you want. So, if the
formula I suggested doesn't do what you wanted I don't have any other suggestions to offer. -- Biff Microsoft Excel MVP "Raget" wrote in message ... =AVERAGE(C5:INDEX(C:C,A1)) doesnt quite give me what i need. is it giving the average from c5 to c(5+A1)? when i edit it, the entire C column is included? What am i missing here? "T. Valko" wrote: Another one: =AVERAGE(C5:INDEX(C:C,A1)) A1 = 10 The formula will average C5:C10 If A1 is empty the entire range will be calculated. -- Biff Microsoft Excel MVP "Raget" wrote in message ... Hi, I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to: have something equal Cx where x=8+ C4
Hard to explain...
There will be a cell - A1. That will be the number of values i want a cell to take into account when it is doing an average. So if i wanted 2 values to make the average, i would put 2 into A1, and it would take into account C5 and C4 when it works out an average to put next to C5. If i wanted 5 values, i would put 5 into A1, and it would take into account C1, C2, C3, C4 and C5 when putting the average next to C5 I have all these values in the C column, so if i were to go down to C20, and i wanted the average of 2 values (that is, C20 and C19), i would have 2 in A1. I can do this graphically, with the moving average trend line. If i could get the values for that that would be the same sort of thing i was looking for. Hope that clears things up. "T. Valko" wrote: I read your reply to Max but I don't understand what you want. So, if the formula I suggested doesn't do what you wanted I don't have any other suggestions to offer. -- Biff Microsoft Excel MVP "Raget" wrote in message ... =AVERAGE(C5:INDEX(C:C,A1)) doesnt quite give me what i need. is it giving the average from c5 to c(5+A1)? when i edit it, the entire C column is included? What am i missing here? "T. Valko" wrote: Another one: =AVERAGE(C5:INDEX(C:C,A1)) A1 = 10 The formula will average C5:C10 If A1 is empty the entire range will be calculated. -- Biff Microsoft Excel MVP "Raget" wrote in message ... Hi, I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to: have something equal Cx where x=8+ C4
How about
=AVERAGE(OFFSET(C5,,,-A1)) -- Regards, Peo Sjoblom "Raget" wrote in message ... Hard to explain... There will be a cell - A1. That will be the number of values i want a cell to take into account when it is doing an average. So if i wanted 2 values to make the average, i would put 2 into A1, and it would take into account C5 and C4 when it works out an average to put next to C5. If i wanted 5 values, i would put 5 into A1, and it would take into account C1, C2, C3, C4 and C5 when putting the average next to C5 I have all these values in the C column, so if i were to go down to C20, and i wanted the average of 2 values (that is, C20 and C19), i would have 2 in A1. I can do this graphically, with the moving average trend line. If i could get the values for that that would be the same sort of thing i was looking for. Hope that clears things up. "T. Valko" wrote: I read your reply to Max but I don't understand what you want. So, if the formula I suggested doesn't do what you wanted I don't have any other suggestions to offer. -- Biff Microsoft Excel MVP "Raget" wrote in message ... =AVERAGE(C5:INDEX(C:C,A1)) doesnt quite give me what i need. is it giving the average from c5 to c(5+A1)? when i edit it, the entire C column is included? What am i missing here? "T. Valko" wrote: Another one: =AVERAGE(C5:INDEX(C:C,A1)) A1 = 10 The formula will average C5:C10 If A1 is empty the entire range will be calculated. -- Biff Microsoft Excel MVP "Raget" wrote in message ... Hi, I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to: have something equal Cx where x=8+ C4
Maybe this:
Assume you have numbers in the range C1:C20 Enter this formula in D1 and copy down to D20: =IF(A$1=0,"",IF(ROWS($1:1)<A$1,"",AVERAGE(OFFSET(C 1,,,-A$1)))) If A1 = 2 D1 = blank D2 = average of C1:C2 D3 = average of C2:C3 D4 = average of C3:C4 If A1 = 5 D1:D4 = blank D5 = average of C1:C5 D6 = average of C2:C6 D7 = average of C3:C7 -- Biff Microsoft Excel MVP "Raget" wrote in message ... Hard to explain... There will be a cell - A1. That will be the number of values i want a cell to take into account when it is doing an average. So if i wanted 2 values to make the average, i would put 2 into A1, and it would take into account C5 and C4 when it works out an average to put next to C5. If i wanted 5 values, i would put 5 into A1, and it would take into account C1, C2, C3, C4 and C5 when putting the average next to C5 I have all these values in the C column, so if i were to go down to C20, and i wanted the average of 2 values (that is, C20 and C19), i would have 2 in A1. I can do this graphically, with the moving average trend line. If i could get the values for that that would be the same sort of thing i was looking for. Hope that clears things up. "T. Valko" wrote: I read your reply to Max but I don't understand what you want. So, if the formula I suggested doesn't do what you wanted I don't have any other suggestions to offer. -- Biff Microsoft Excel MVP "Raget" wrote in message ... =AVERAGE(C5:INDEX(C:C,A1)) doesnt quite give me what i need. is it giving the average from c5 to c(5+A1)? when i edit it, the entire C column is included? What am i missing here? "T. Valko" wrote: Another one: =AVERAGE(C5:INDEX(C:C,A1)) A1 = 10 The formula will average C5:C10 If A1 is empty the entire range will be calculated. -- Biff Microsoft Excel MVP "Raget" wrote in message ... Hi, I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to: have something equal Cx where x=8+ C4
Yeh works great
Thanks everyone for your help, great fast responses! Im amazed! "Peo Sjoblom" wrote: How about =AVERAGE(OFFSET(C5,,,-A1)) -- Regards, Peo Sjoblom "Raget" wrote in message ... Hard to explain... There will be a cell - A1. That will be the number of values i want a cell to take into account when it is doing an average. So if i wanted 2 values to make the average, i would put 2 into A1, and it would take into account C5 and C4 when it works out an average to put next to C5. If i wanted 5 values, i would put 5 into A1, and it would take into account C1, C2, C3, C4 and C5 when putting the average next to C5 I have all these values in the C column, so if i were to go down to C20, and i wanted the average of 2 values (that is, C20 and C19), i would have 2 in A1. I can do this graphically, with the moving average trend line. If i could get the values for that that would be the same sort of thing i was looking for. Hope that clears things up. "T. Valko" wrote: I read your reply to Max but I don't understand what you want. So, if the formula I suggested doesn't do what you wanted I don't have any other suggestions to offer. -- Biff Microsoft Excel MVP "Raget" wrote in message ... =AVERAGE(C5:INDEX(C:C,A1)) doesnt quite give me what i need. is it giving the average from c5 to c(5+A1)? when i edit it, the entire C column is included? What am i missing here? "T. Valko" wrote: Another one: =AVERAGE(C5:INDEX(C:C,A1)) A1 = 10 The formula will average C5:C10 If A1 is empty the entire range will be calculated. -- Biff Microsoft Excel MVP "Raget" wrote in message ... Hi, I am sure there is a really simple solution to this one. I have a cell that equals an average, eg =Average(C5:C10), which i have filled for about 400 rows. I want to easily have one cell where i can edit a value, say from 5 to 10, which would then increase the difference between C5 and C10 to C5 and C15. I am pretty new to excel, dont have much of an idea, but i would like to avoid having to refill every time i want to change that value. I attemped =average(C5:C(5+A1)) where A1 equaled the value, but that just returned with an error. Any help is appretiated |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can I make cell "yes" equal 1, "no" equal 0 | Excel Discussion (Misc queries) | |||
| #ref to equal zero | Excel Worksheet Functions | |||
| Contains instead of Equal | Excel Worksheet Functions | |||
| not equal to zero | Excel Worksheet Functions | |||
| Getting 0 to equal 1 | Excel Worksheet Functions | |||
Linear Mode
