Sum Of Last Three Entries In Rotating Columns
 

Hi, I entered the years in row 4, 2006, 2007 etc€¦ starting in E4 and working
right. I then entered Jan thru to Dec in Column D, starting at D5. At the end
of each month I enter a value within the spreadsheet Corresponding to the
Month and Year. How do I get the sum of the last three months when the value
data is entered down one column and then starts back up and down the next
column to the right?

Sum Of Last Three Entries In Rotating Columns
 

So let's assume that your years extend as far as J4 and therefore your
numbers are in E5:J16. This formula will sum the last 3 entries irrespective
of the current date:


=SUM(IF(COLUMN(E5:J16)*100+ROW(E5:J16)LARGE((COLU MN(E5:J16)*100+ROW(E5:J16))*(E5:J16<""),4),E5:J16 ))

This is an "array formula" which must be confirmed with CTRL+SHIFT+ENTER so
that curly braces like { and } appear around the formula in the formula bar

Note: assumes that there won't be gaps in the data


"Loadmaster" wrote:

Hi, I entered the years in row 4, 2006, 2007 etc€¦ starting in E4 and working
right. I then entered Jan thru to Dec in Column D, starting at D5. At the end
of each month I enter a value within the spreadsheet Corresponding to the
Month and Year. How do I get the sum of the last three months when the value
data is entered down one column and then starts back up and down the next
column to the right?

Sum Of Last Three Entries In Rotating Columns
 

That formula worked great, however I have one more question WRT the same
spreadsheet. How do I retrieve the last entry from the last row and column?

"daddylonglegs" wrote:

So let's assume that your years extend as far as J4 and therefore your
numbers are in E5:J16. This formula will sum the last 3 entries irrespective
of the current date:


=SUM(IF(COLUMN(E5:J16)*100+ROW(E5:J16)LARGE((COLU MN(E5:J16)*100+ROW(E5:J16))*(E5:J16<""),4),E5:J16 ))

This is an "array formula" which must be confirmed with CTRL+SHIFT+ENTER so
that curly braces like { and } appear around the formula in the formula bar

Note: assumes that there won't be gaps in the data


"Loadmaster" wrote:

Hi, I entered the years in row 4, 2006, 2007 etc€¦ starting in E4 and working
right. I then entered Jan thru to Dec in Column D, starting at D5. At the end
of each month I enter a value within the spreadsheet Corresponding to the
Month and Year. How do I get the sum of the last three months when the value
data is entered down one column and then starts back up and down the next
column to the right?

Sum Of Last Three Entries In Rotating Columns
 

=LOOKUP(2,1/(A1:A20000<""),A1:A20000)

for the last row in A1:A20000

adapt to fit


=LOOKUP(2,1/(2:2<""),2:2)

last column in row 2


--


Regards,


Peo Sjoblom






"Loadmaster" wrote in message
...
That formula worked great, however I have one more question WRT the same
spreadsheet. How do I retrieve the last entry from the last row and
column?

Sum Of Last Three Entries In Rotating Columns
 

What I meant to say was enter the row within the last column not both the
last column and last row.

"Peo Sjoblom" wrote:

=LOOKUP(2,1/(A1:A20000<""),A1:A20000)

for the last row in A1:A20000

adapt to fit


=LOOKUP(2,1/(2:2<""),2:2)

last column in row 2


--


Regards,


Peo Sjoblom






"Loadmaster" wrote in message
...
That formula worked great, however I have one more question WRT the same
spreadsheet. How do I retrieve the last entry from the last row and
column?

Sum Of Last Three Entries In Rotating Columns
 

For the last entry only try

=LOOKUP(9.999999999999E+307,INDEX(E5:J16,0,MAX(IF( E5:J16<"",COLUMN(E5:J16)-COLUMN(E5)+1))))

"Peo Sjoblom" wrote:

=LOOKUP(2,1/(A1:A20000<""),A1:A20000)

for the last row in A1:A20000

adapt to fit


=LOOKUP(2,1/(2:2<""),2:2)

last column in row 2


--


Regards,


Peo Sjoblom






"Loadmaster" wrote in message
...
That formula worked great, however I have one more question WRT the same
spreadsheet. How do I retrieve the last entry from the last row and
column?

Sum Of Last Three Entries In Rotating Columns
 

Thank-you, that formula also worked great.

"daddylonglegs" wrote:

For the last entry only try

=LOOKUP(9.999999999999E+307,INDEX(E5:J16,0,MAX(IF( E5:J16<"",COLUMN(E5:J16)-COLUMN(E5)+1))))

"Peo Sjoblom" wrote:

=LOOKUP(2,1/(A1:A20000<""),A1:A20000)

for the last row in A1:A20000

adapt to fit


=LOOKUP(2,1/(2:2<""),2:2)

last column in row 2


--


Regards,


Peo Sjoblom






"Loadmaster" wrote in message
...
That formula worked great, however I have one more question WRT the same
spreadsheet. How do I retrieve the last entry from the last row and
column?