Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
I'd really appreciate getting some help with one formula I'm working
on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
The formula
= AVERAGE( range ) will return the average of the numbers in that range. It is identical to the formula = SUM ( range ) / COUNT ( range ) Your formula does not average anything. If I put your formula in a cell in column C then I get results that could be obtained by = C4/C2 * 100 If I copy that across to all cells in that row in cols C to J I get the same results that you get. You do say that you are not getting the correct results but do not say what is wrong or what you want. I cannot work out why you are using AVERAGE anyway. How about you say what values you have and what results you want and where these values are. What I think you have is a table with values in the top row and you want to get results from different rows divided by the top row value - but that it just a guess. Chrissy. Eric G wrote I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
=AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100
Entered with Ctrl+shift+enter rather than just Enter because this is an array formula Should do what you want. -- Regards, Tom Ogilvy Eric G wrote in message ... I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
Thanks Tom,
I haven't tried it yet. I'm just wondering what the IF((C4:J4<"")* <------ multiplication (*) sign is for? Not really sure what the first two parts of the formula are for. Thanks Eric On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy" wrote: =AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100 Entered with Ctrl+shift+enter rather than just Enter because this is an array formula Should do what you want. -- Regards, Tom Ogilvy Eric G wrote in message ... I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
(C4:J4<"")
C4: 100 D4: 3 E4: F4: G4: 10 H4: J4: produces {True,True,False,False,True,False,False} C2: 1000 D2: E2: 300 F2: G2: 50 H2: J2: 1000 produces {True,False,True,False,True,False,True} when we multiply these together, the Trues are coerced to 1's and the Falses are coerced to zeros {1,1,0,0,1,0,0} * {1,0,1,0,1,0,1} = {1,0,0,0,1,0,0} the if statement interprets 1 as True and zero as False, so the if returns {100/1000,False,False,False,10/50,False,False} The Average function ignores the False and takes the average of 100/1000 and 10/50 You can go to the formula bar and highlight complete subexpressions and the formula and do F9 to have that subexpression evaluated (hit esc to return the formula, don't hit enter or the subexpression will be replaced with the evaluated values). -- Regards, Tom Ogilvy Eric G wrote in message ... Thanks Tom, I haven't tried it yet. I'm just wondering what the IF((C4:J4<"")* <------ multiplication (*) sign is for? Not really sure what the first two parts of the formula are for. Thanks Eric On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy" wrote: =AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100 Entered with Ctrl+shift+enter rather than just Enter because this is an array formula Should do what you want. -- Regards, Tom Ogilvy Eric G wrote in message ... I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
Thanks Tom,
I haven't tried it yet. I'm just wondering what the IF((C4:J4<"")* <------ multiplication (*) sign is for? Not really sure what the first two parts of the formula are for. Thanks Eric On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy" wrote: =AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100 Entered with Ctrl+shift+enter rather than just Enter because this is an array formula Should do what you want. -- Regards, Tom Ogilvy Eric G wrote in message ... I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
What were you trying to do with your original formula?
What Tom suggests will give an average but it may not be what you want as you did not clarify it. What his formula gives is the average of each pair of numbers in your two ranges, C2:J2 and C4:J4. It divides each number in row 4 by the number in that column in row 2 and then averages these results. It then multiplies that average by 100. Specifically what his formula does is gets the number in each cell in row 4 and tests to see if there is something in the cell. If there is then it is TRUE and if not then it is FALSE. It does this with row 2 also. It just happens that in Excel (and most other computer applications) that TRUE = 1 and FALSE = 0. It then multiplies these two numbers together and give one of 0*0 0*1 1*0 1*1 The only one of those to give an answer of 1 is the last one. That is for any pair of cells in rows 2 and 4 where there is a number in both. As 1 = TRUE then when there is a number in both rows of a column then the next bit of the formula, the bit after the comma, is the bit that is used. If there is a 0 then FALSE is used. The final result is of this part is then multiplied by 100. =AVERAGE( of each pair of numbers from rows 2 and 4 )*100 where, if there is a number in row 2 and 4 in the column each pair of numbers from rows 2 and 4 is the number in row 4 divided by the number in row 2 otherwise 0 Entering it with ATL+Enter gives an array formula so you get more than one result - one per each cell in the range - and you average all these numbers. Does that help you understand the formula? Is that what you want? If it is not what you want then tell us the numbers you have as an example and what result you want and it will probably just mean shuffling things in Tom's formula a bit. Chrissy. "Eric G" wrote in message ... Thanks Tom, I haven't tried it yet. I'm just wondering what the IF((C4:J4<"")* <------ multiplication (*) sign is for? Not really sure what the first two parts of the formula are for. Thanks Eric On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy" wrote: =AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100 Entered with Ctrl+shift+enter rather than just Enter because this is an array formula Should do what you want. -- Regards, Tom Ogilvy Eric G wrote in message ... I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
In the formula bar, highlight c4:j4<""
then hit F9. You'll see something like: {FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE} Now do the same thing with C2:j2<"" and hit f9 You'll see the same kind of thing: {FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE} Hit escape (to reset the formula). Now hightlight (C4:J4<"")*(C2:J2<"") and hit F9 You'll see something like: {0,1,0,1,0,1,0,0} The formula is doing an "AND". The 0's are when either are false, the 1's are when both are True. So Tom's formula kind of gets translated into: For each element (c4, d4, e4, ...,J4 and c2, ....,j2) look to see if both are non-blank. If they are non-blank the use the division (X4/X2--C2&4, D2&4, etc). If either is blank (or both are blank), then use False (not explicitly included in Tom's original formula, but there implicitly). (And False doesn't upset the Average calculation.) Then multiply the whole thing by 100. Eric G wrote: Thanks Tom, I haven't tried it yet. I'm just wondering what the IF((C4:J4<"")* <------ multiplication (*) sign is for? Not really sure what the first two parts of the formula are for. Thanks Eric On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy" wrote: =AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100 Entered with Ctrl+shift+enter rather than just Enter because this is an array formula Should do what you want. -- Regards, Tom Ogilvy Eric G wrote in message ... I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
My explanation isn't visible ?
-- Regards, Tom Ogilvy Dave Peterson wrote in message ... In the formula bar, highlight c4:j4<"" then hit F9. You'll see something like: {FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE} Now do the same thing with C2:j2<"" and hit f9 You'll see the same kind of thing: {FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE} Hit escape (to reset the formula). Now hightlight (C4:J4<"")*(C2:J2<"") and hit F9 You'll see something like: {0,1,0,1,0,1,0,0} The formula is doing an "AND". The 0's are when either are false, the 1's are when both are True. So Tom's formula kind of gets translated into: For each element (c4, d4, e4, ...,J4 and c2, ....,j2) look to see if both are non-blank. If they are non-blank the use the division (X4/X2--C2&4, D2&4, etc). If either is blank (or both are blank), then use False (not explicitly included in Tom's original formula, but there implicitly). (And False doesn't upset the Average calculation.) Then multiply the whole thing by 100. Eric G wrote: Thanks Tom, I haven't tried it yet. I'm just wondering what the IF((C4:J4<"")* <------ multiplication (*) sign is for? Not really sure what the first two parts of the formula are for. Thanks Eric On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy" wrote: =AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100 Entered with Ctrl+shift+enter rather than just Enter because this is an array formula Should do what you want. -- Regards, Tom Ogilvy Eric G wrote in message ... I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
Hi Dave,
Thanks very much for your great detailed explanation! I understood it all and am ready to give it a whirl now. Much appreciated! Eric On Mon, 13 Oct 2003 20:02:50 -0500, Dave Peterson wrote: In the formula bar, highlight c4:j4<"" then hit F9. You'll see something like: {FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE} Now do the same thing with C2:j2<"" and hit f9 You'll see the same kind of thing: {FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE} Hit escape (to reset the formula). Now hightlight (C4:J4<"")*(C2:J2<"") and hit F9 You'll see something like: {0,1,0,1,0,1,0,0} The formula is doing an "AND". The 0's are when either are false, the 1's are when both are True. So Tom's formula kind of gets translated into: For each element (c4, d4, e4, ...,J4 and c2, ....,j2) look to see if both are non-blank. If they are non-blank the use the division (X4/X2--C2&4, D2&4, etc). If either is blank (or both are blank), then use False (not explicitly included in Tom's original formula, but there implicitly). (And False doesn't upset the Average calculation.) Then multiply the whole thing by 100. Eric G wrote: Thanks Tom, I haven't tried it yet. I'm just wondering what the IF((C4:J4<"")* <------ multiplication (*) sign is for? Not really sure what the first two parts of the formula are for. Thanks Eric On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy" wrote: =AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100 Entered with Ctrl+shift+enter rather than just Enter because this is an array formula Should do what you want. -- Regards, Tom Ogilvy |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
Hi Tom,
I tried out your formula yesterday and it worked very nicely. Thanks again for your help! Eric On Fri, 10 Oct 2003 23:55:06 -0400, "Tom Ogilvy" wrote: =AVERAGE(IF((C4:J4<"")*(C2:J2<""),C4:J4/C2:J2))*100 Entered with Ctrl+shift+enter rather than just Enter because this is an array formula Should do what you want. -- Regards, Tom Ogilvy Eric G wrote in message ... I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula?
Thanks Chrissy,
Tom helped me out with the formula and I've got it working now. Eric On Sat, 11 Oct 2003 14:19:27 +1300, "Chrissy" wrote: The formula = AVERAGE( range ) will return the average of the numbers in that range. It is identical to the formula = SUM ( range ) / COUNT ( range ) Your formula does not average anything. If I put your formula in a cell in column C then I get results that could be obtained by = C4/C2 * 100 If I copy that across to all cells in that row in cols C to J I get the same results that you get. You do say that you are not getting the correct results but do not say what is wrong or what you want. I cannot work out why you are using AVERAGE anyway. How about you say what values you have and what results you want and where these values are. What I think you have is a table with values in the top row and you want to get results from different rows divided by the top row value - but that it just a guess. Chrissy. Eric G wrote I'd really appreciate getting some help with one formula I'm working on, if possible. Something like: AVERAGE(C4:J4/C$2:J$2*100) Right now I sort of have it working. The only problem is that with values at the moment in only C4:E4 it's giving me a 'false' average. I could restrict the formula to the cells that have values but it would be very tedious to keep changing it each time I add a value to F4, G4 etc. TIA Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |