Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a 'calculated' array in a function?
for example, I have 2 columns of data:
3 6 4 2 1 1 7 3 I want to RANK the sum of each row but do not want to store the sum in another column first. the sums a 9 6 2 10 so in column C, I want to see 3 2 1 4 Resulting sheet would look like: 3 6 3 4 2 2 1 1 1 7 3 4 I tried this for the formula in C and it did not like it ($ present to make it easy for fill down): =RANK(a1+b1,($a$1+$b$1):($a$4+$b$4),1) I tried SHIFT+CTRL+ENTER thinking it might be an array formula but that did not work either. Again, the crux of my dilemma is that I do not want to store the sum in another column prior to ranking. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a 'calculated' array in a function?
Try this...
Entered in C1 and copied down to C4: =SUMPRODUCT(--(A1+B1A$1:A$4+B$1:B$4))+1 -- Biff Microsoft Excel MVP "CalculatedArray" wrote in message ... for example, I have 2 columns of data: 3 6 4 2 1 1 7 3 I want to RANK the sum of each row but do not want to store the sum in another column first. the sums a 9 6 2 10 so in column C, I want to see 3 2 1 4 Resulting sheet would look like: 3 6 3 4 2 2 1 1 1 7 3 4 I tried this for the formula in C and it did not like it ($ present to make it easy for fill down): =RANK(a1+b1,($a$1+$b$1):($a$4+$b$4),1) I tried SHIFT+CTRL+ENTER thinking it might be an array formula but that did not work either. Again, the crux of my dilemma is that I do not want to store the sum in another column prior to ranking. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a 'calculated' array in a function?
A beautiful formula!
-- Gary''s Student - gsnu200829 "T. Valko" wrote: Try this... Entered in C1 and copied down to C4: =SUMPRODUCT(--(A1+B1A$1:A$4+B$1:B$4))+1 -- Biff Microsoft Excel MVP "CalculatedArray" wrote in message ... for example, I have 2 columns of data: 3 6 4 2 1 1 7 3 I want to RANK the sum of each row but do not want to store the sum in another column first. the sums a 9 6 2 10 so in column C, I want to see 3 2 1 4 Resulting sheet would look like: 3 6 3 4 2 2 1 1 1 7 3 4 I tried this for the formula in C and it did not like it ($ present to make it easy for fill down): =RANK(a1+b1,($a$1+$b$1):($a$4+$b$4),1) I tried SHIFT+CTRL+ENTER thinking it might be an array formula but that did not work either. Again, the crux of my dilemma is that I do not want to store the sum in another column prior to ranking. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use a 'calculated' array in a function?
Thanks!
-- Biff Microsoft Excel MVP "Gary''s Student" wrote in message ... A beautiful formula! -- Gary''s Student - gsnu200829 "T. Valko" wrote: Try this... Entered in C1 and copied down to C4: =SUMPRODUCT(--(A1+B1A$1:A$4+B$1:B$4))+1 -- Biff Microsoft Excel MVP "CalculatedArray" wrote in message ... for example, I have 2 columns of data: 3 6 4 2 1 1 7 3 I want to RANK the sum of each row but do not want to store the sum in another column first. the sums a 9 6 2 10 so in column C, I want to see 3 2 1 4 Resulting sheet would look like: 3 6 3 4 2 2 1 1 1 7 3 4 I tried this for the formula in C and it did not like it ($ present to make it easy for fill down): =RANK(a1+b1,($a$1+$b$1):($a$4+$b$4),1) I tried SHIFT+CTRL+ENTER thinking it might be an array formula but that did not work either. Again, the crux of my dilemma is that I do not want to store the sum in another column prior to ranking. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Has @DAYS 360 function calculated differently between qtr and mon | Excel Worksheet Functions | |||
how to sort a range which is calculated by array formula | Excel Discussion (Misc queries) | |||
Can I use IF function in calculated fields in a pivot table? | Excel Worksheet Functions | |||
Help Please! - Creating Function Calculated Fields within Pivot Tables | Excel Worksheet Functions | |||
Can Function results be calculated? | New Users to Excel |