 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2007Refer to one row, take value from another
Hi, I would like to display, in one cell, the average of all values in row 2 where "beg" appears in the same column of row 1. For some reason I am incapable of getting the search terms for this correct - I'm sure this must have been covered in a previous post! Thanks in advance, Paul Hi Try the array entered formula {=AVERAGE(IF(1:1="beg",2:2,""))} To enter or amend array formulae, use Control + Shift + Enter (CSE) as opposed to Enter Do not type the curly braces { } yourself. If you use CSE, Excel will insert them for you.  SignatureRegards Roger Govier > Hi, > [quoted text clipped - 7 lines] > > Paul That's great - thank you! I was playing with LOOKUP functions and getting nowhere! A problem I didn't forsee was that there are zero values in row 2 which I need to ignore to get an accurate result. How can I do this? An embedded IF statement? On Dec 9, 1:49 pm, "Roger Govier" <roger@technology4unospamdotcodotuk> wrote: > Hi > [quoted text clipped - 22 lines] > > > Paul =AVERAGE(IF((1:1="beg")*(2:2<>0),2:2,"")) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > That's great - thank you! I was playing with LOOKUP functions and > getting nowhere! [quoted text clipped - 31 lines] >> >> > Paul Unfortunately, that gave me the same result! > =AVERAGE(IF((1:1="beg")*(2:2<>0),2:2,"")) > [quoted text clipped - 42 lines] > > >> > Paul I just retested. Details? SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Unfortunately, that gave me the same result! > [quoted text clipped - 50 lines] >> >> >> > Paul Hi Don I have only just seen your response. Yes it works fine. It is better than my second response to the OP. SignatureRegards Roger Govier > I just retested. Details? > [quoted text clipped - 53 lines] >>> >>> >> > Paul I don't really know what details to give! The final figure was lower than expected and, when I checked, saw it was the average of all the values including zeros. Thank you both for your later responses but I have now realised that there is a better way for me to deal with this problem. The zeros that are being counted are caused by a reference to an empty cell. However, there could be times that a zero is the 'real' figure required and by eliminating all zeros I will prevent the spreadsheet from working correctly. Instead of this, I will add an IF statement to the first formula so that ISEMPTY cells will result in "", rather than zero. This way, 'real' zeros will be taken into account and artifactual zeros will not. Thank you both again for your time, Paul > I just retested. Details? > [quoted text clipped - 57 lines] > > >> >> > Paul ISBLANK, I mean! > I don't really know what details to give! The final figure was lower > than expected and, when I checked, saw it was the average of all the [quoted text clipped - 78 lines] > > > >> >> > Paul Hold on, I thought all was well, but actually there's still a problem with the original formula. Let me paste it 'as is' and give you the real situation. I have a row with an alternating set of three columns, labelled (in row 5) Beg, Mid and End. In row 24 I would like to have the average for all Beg columns, for all Mid columns and for all End columns. I therefore have the following formulae in three adjacent cells: =AVERAGE(IF('Cash flow'!5:5="Beg",'Cash flow'!24:24,"")) =AVERAGE(IF('Cash flow'!5:5="Mid",'Cash flow'!24:24,"")) =AVERAGE(IF('Cash flow'!5:5="End",'Cash flow'!24:24,"")) Strangely, the figure comes out as the same for all three, even when there is only one value in the entire Cash flow'!24:24 row (it's in a Beg column. Do these formulae really instruct Excel to average only values in the Beg (or Mid or End) columns? Thanks again, Paul > I don't really know what details to give! The final figure was lower > than expected and, when I checked, saw it was the average of all the [quoted text clipped - 78 lines] > > > >> >> > Paul The formula below needs to be ARRAY entered using ctrl+shift+enter vs just enter. Then it may be copied if desired. It will average ROW 24 for values in ROW 5 having "Beg". IF?? you want columns modify to suit and make sure that you do NOT include the entire column. Also, best for rows too. What do you want? =AVERAGE(IF('Cash flow'!5:5="Beg",'Cash flow'!24:24,"")) rows =AVERAGE(IF('Cash flow'!a5:iv5="Beg",'Cash flow'!a24:iv24,"")) for columns =AVERAGE(IF((b3:b33="beg")*(a3:a33<>0),a3:a33,"")) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hold on, > [quoted text clipped - 109 lines] >> >> > >> >> > Paul Hi Don, I thought maybe this post had dropped off the radar so followed it up in another post. I think the problem was that, although I was 'array entering' originally, after copying and pasting the fomula into other cells, I was merely changing Beg to Mid/End etc. Yes, I'm an idiot! Anyway, it's sorted now. Well, that bit is - I've just posted another question now! Thanks again for all your time, Paul > The formula below needs to be ARRAY entered using ctrl+shift+enter vs just > enter. Then it may be copied if desired. It will average ROW 24 for values [quoted text clipped - 126 lines] > > >> > >> >> > Paul Hi Don, I thought maybe this post had dropped off the radar so followed it up in another post. I think the problem was that, although I was 'array entering' originally, after copying and pasting the fomula into other cells, I was merely changing Beg to Mid/End etc. Yes, I'm an idiot! Anyway, it's sorted now. Well, that bit is - I've just posted another question now! Thanks again for all your time, Paul > The formula below needs to be ARRAY entered using ctrl+shift+enter vs just > enter. Then it may be copied if desired. It will average ROW 24 for values [quoted text clipped - 126 lines] > > >> > >> >> > Paul Hi Don, I thought maybe this post had dropped off the radar so followed it up in another post. I think the problem was that, although I was 'array entering' originally, after copying and pasting the fomula into other cells, I was merely changing Beg to Mid/End etc. Yes, I'm an idiot! Anyway, it's sorted now. Well, that bit is - I've just posted another question now! Thanks again for all your time, Paul > The formula below needs to be ARRAY entered using ctrl+shift+enter vs just > enter. Then it may be copied if desired. It will average ROW 24 for values [quoted text clipped - 126 lines] > > >> > >> >> > Paul OK. I got this reply 3 times. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi Don, > [quoted text clipped - 155 lines] >> >> >> > >> >> > Paul Hi Try {=SUM((A1:G1="beg")*(A2:G2>0)*A2:G2)/SUM((A1:G1="beg")*(A2:G2>0))} again array entered SignatureRegards Roger Govier > That's great - thank you! I was playing with LOOKUP functions and > getting nowhere! [quoted text clipped - 31 lines] >> >> > Paul |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.