Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function returning SUM of 2 adjacent cols in 2 separate cells
Hi everyone, I need some help with this please; I am using Excell 2007
Worksheet contains 6 cols, T,U,V,W,X and Y and another col AG in a small recap table a few columns outside the main worksheet. Col W contains a formula (copied and pasted right down the col): =IF(T2="YES",(U2)*V2," ") returning a + figure. Col X contains a formula (again copied and pasted right down the col): =IF(T2="NO",-V2," ") returning a - figure. Col Y contains a formula (in one cell only at the end of a variable series of row (ranging from 4 to 25+) =SUM(W2:W12)+(SUM(X2:X12) returning the total of cols W and X for that particular block of rows (in this instance, positioned in row Y12). Cols T, U and V are blanks and data is entered as needed which prompts cols W, X and Y to return a value. Example: Col T Col U Col V Col W Col X Col Y Col AG Row 2 blank blank blank blank blank blank 2600 Row 3 Yes 30 100 3000 blank blank 2000 Row 4 No blank 450 blank -450 blank Row 5 blank blank blank blank blank blank Row 6 Yes 50 20 100 blank blank Row 7 No blank 50 blank -50 2600 Row 8 blank blank blank blank blank blank etc etc Row 18 yes 100 20 2000 blank 2000 etc etc And so on, with a total in col Y for each "block" of data. What I am doing: Copy and paste my formulae in cols W and X; then, manually counting the total of rows in each block, enter the SUM formula in the "framed" cell in col Y, at the end of each sequence (which can vary from 4 rows to 25 or more); I then make a note of the cell row number and enter an =Y... in column AG of the recap table (for instance in the example above, in AG2, I will enter: =Y7; in AG3, I will enter =Y18, and so on for each block, in AG4, AG5, etc.). I hope this is clear ! What I would like: Is to automate the process so that the SUM total of cols W and X will be calculated in the appropriate cell in col Y and be replicated in col AG - without having to resort to the manual process. Is this possible? The amount or rows in each block is randomly different - Data is imported and sorted chronologically before copying into worksheet. Thank you very much for any help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function returning SUM of 2 adjacent cols in 2 separate cells
Here's some thoughts for the 2nd Q in your post, ie a simple play to
dynamically extract col Y's interspersed results directly into col AG, all neatly bunched at the top as desired In AF2: =IF(Y2="","",ROW()) Leave AF1 empty In AG2: =IF(ROWS($1:1)COUNT(AF:AF),"",INDEX(Y:Y,SMALL(AF: AF,ROWS($1:1)))) Select AF2:AG2, copy down to cover the max expected extent of col Y, say down to AG200? AG2 will return the required results from col Y, all neatly packed at the top. Minimize/hide away col AF. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:57 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function returning SUM of 2 adjacent cols in 2 separate cells
Thank you very much Max. I will give it a try. So If I have understood
correctly, I type the short formula in AF2, leave AF1 empty; type the long formula in AG2, highlight both AF2 and AG2 and copy down the column (it will be a max of 50); is that correct? It now leaves me with the (possible?) automated SUM calculation in column Y, before I can try yours in my recap table. Thank you very much for your time and I hope you have a good day. "Max" wrote: Here's some thoughts for the 2nd Q in your post, ie a simple play to dynamically extract col Y's interspersed results directly into col AG, all neatly bunched at the top as desired In AF2: =IF(Y2="","",ROW()) Leave AF1 empty In AG2: =IF(ROWS($1:1)COUNT(AF:AF),"",INDEX(Y:Y,SMALL(AF: AF,ROWS($1:1)))) Select AF2:AG2, copy down to cover the max expected extent of col Y, say down to AG200? AG2 will return the required results from col Y, all neatly packed at the top. Minimize/hide away col AF. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:57 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function returning SUM of 2 adjacent cols in 2 separate cells
Yes, but please don't re-type. Just copy direct from my posting the formulas
and paste directly into the respective cells' formula bars. If you re-type, you're wasting effort and likely to have typos creep-in, etc. The formulas as posted have been tested here and should work fine for you over there. I didn't venture any thoughts on your 1st Q as I could not figure out how's the association between your data cols T - V and where the auto sum is needed for each "block". It's something you do easily manually (albeit tiresome) but it may not be as easy to get Excel to do likewise. If nobody else pops by here, you could try putting in a new posting on this issue, with perhaps more elaboration. Lastly pl take a moment to press the "Yes" button (like the ones below) in all responses which help -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:57 xdemechanik --- "CAT" wrote: Thank you very much Max. I will give it a try. So If I have understood correctly, I type the short formula in AF2, leave AF1 empty; type the long formula in AG2, highlight both AF2 and AG2 and copy down the column (it will be a max of 50); is that correct? It now leaves me with the (possible?) automated SUM calculation in column Y, before I can try yours in my recap table. Thank you very much for your time and I hope you have a good day. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function returning SUM of 2 adjacent cols in 2 separate cells
Ah, I just realized that you posted a similar question in .misc, and you've
got response there as well. Pl refrain from doing this "multi-posting". Stick to one posting in one particular newsgroup. Most of the regular responders will read the popular newsgroups (such as this, .misc & .newusers) so you won't lose out catching their attention by posting only once in one of these newsgroups. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:57 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function returning SUM of 2 adjacent cols in 2 separate cells
Hi Max,
I'm new here; so apologies for the "error of conduct". I actually posted on General and did not get an answer as such, but a need for more info, so I thought that posting my query on Functions would get me somewhere. I just wanted to say a big thank you for your answer by the way: I have just applied your formulae to my present workbook, filled in the Y cells manually and it WORKS - Every totals are returned in neat order in my checkup table, including the header of col Y! Thank you again for your help. Any chance of resolving part 1 of my query? Sorry to be so demanding! "Max" wrote: Ah, I just realized that you posted a similar question in .misc, and you've got response there as well. Pl refrain from doing this "multi-posting". Stick to one posting in one particular newsgroup. Most of the regular responders will read the popular newsgroups (such as this, .misc & .newusers) so you won't lose out catching their attention by posting only once in one of these newsgroups. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:57 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function returning SUM of 2 adjacent cols in 2 separate cells
Welcome, but pl press the "Yes" button below
since the responses helped, won't you? On your Q1, I've explained it in my earlier response. I don't have any further views -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:57 xdemechanik --- "CAT" wrote: Hi Max, I'm new here; so apologies for the "error of conduct". I actually posted on General and did not get an answer as such, but a need for more info, so I thought that posting my query on Functions would get me somewhere. I just wanted to say a big thank you for your answer by the way: I have just applied your formulae to my present workbook, filled in the Y cells manually and it WORKS - Every totals are returned in neat order in my checkup table, including the header of col Y! Thank you again for your help. Any chance of resolving part 1 of my query? Sorry to be so demanding! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function returning SUM of 2 adjacent cols in 2 separate cells
Hi Max,
Sorry again, I hadn't read your preceding reply. The reason I did not press Yes is because of the unsolved part 1 of my query. I will wait a little and if I don't get an answer, will close this thread and start another whilst trying to clarify things a bit. Thank you again. "CAT" wrote: Hi Max, I'm new here; so apologies for the "error of conduct". I actually posted on General and did not get an answer as such, but a need for more info, so I thought that posting my query on Functions would get me somewhere. I just wanted to say a big thank you for your answer by the way: I have just applied your formulae to my present workbook, filled in the Y cells manually and it WORKS - Every totals are returned in neat order in my checkup table, including the header of col Y! Thank you again for your help. Any chance of resolving part 1 of my query? Sorry to be so demanding! "Max" wrote: Ah, I just realized that you posted a similar question in .misc, and you've got response there as well. Pl refrain from doing this "multi-posting". Stick to one posting in one particular newsgroup. Most of the regular responders will read the popular newsgroups (such as this, .misc & .newusers) so you won't lose out catching their attention by posting only once in one of these newsgroups. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:57 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell returning totals of 2 cols+ sending this value to another col | Excel Discussion (Misc queries) | |||
Returning adjacent values | Excel Discussion (Misc queries) | |||
SUM function won't allow more than 30 non-adjacent cells | Excel Worksheet Functions | |||
How to use SUMIF function with non-adjacent cells | Excel Worksheet Functions | |||
Cond Format:re color 2 cols, skip 2 cols | Excel Worksheet Functions |