sliding calculation
I am trying to keep a log of Flight time and would always like to know what
I've flown within the last 90 days of the current date in several different modes of flight. A B C D 1 Date day night NVG 2 2 Sep 06 1.5 2.4 3 15 Sep 06 1.2 2.6 4 22 Sep 06 2.0 4.0 What is the formula to automatically calculate? |
sliding calculation
Try:
Day: =SUMPRODUCT(($A$2:$A$100=TODAY()-90)*(B2:B100)) Night: =SUMPRODUCT(($A$2:$A$100=TODAY()-90)*(C2:C100)) NVG: =SUMPRODUCT(($A$2:$A$100=TODAY()-90)*(D2:D100)) Adjust the range to suit. The absolute ranges ($A$2:$A$100) are so that I could copy the Day formula across the other two columns. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "KiowaPilot" wrote in message ... I am trying to keep a log of Flight time and would always like to know what I've flown within the last 90 days of the current date in several different modes of flight. A B C D 1 Date day night NVG 2 2 Sep 06 1.5 2.4 3 15 Sep 06 1.2 2.6 4 22 Sep 06 2.0 4.0 What is the formula to automatically calculate? |
sliding calculation
ON MY FILE THAT I WAS WORKING ON,
i WAS TOYING WITH =SUMIF(D:D, =E2, B:B) D:D BEING THE DATE IN NUMBER FORMAT IN A HIDDEN COLUMN E2 BEING TODAY()-180 IN NUMBER FORMAT B:B THE FLIGHT TIME ENTERED THE PROBLEM WITH THIS THOUGH IS THAT THE CRITERIA WILL NOT ALLOW A FIELD ADDRESS E2 IN CONJUCTION WITH = BUT IT WILL ALLOW THEM TO STAND ALONE. IT WORKS PERFECT IF I TYPE IN =38800 38800 IS (20 SEPT 2006) "Sandy Mann" wrote: Try: Day: =SUMPRODUCT(($A$2:$A$100=TODAY()-90)*(B2:B100)) Night: =SUMPRODUCT(($A$2:$A$100=TODAY()-90)*(C2:C100)) NVG: =SUMPRODUCT(($A$2:$A$100=TODAY()-90)*(D2:D100)) Adjust the range to suit. The absolute ranges ($A$2:$A$100) are so that I could copy the Day formula across the other two columns. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "KiowaPilot" wrote in message ... I am trying to keep a log of Flight time and would always like to know what I've flown within the last 90 days of the current date in several different modes of flight. A B C D 1 Date day night NVG 2 2 Sep 06 1.5 2.4 3 15 Sep 06 1.2 2.6 4 22 Sep 06 2.0 4.0 What is the formula to automatically calculate? |
sliding calculation
Try this, then:
=SUMIF(D:D, "="&E2, B:B) Hope this helps. Pete KiowaPilot wrote: ON MY FILE THAT I WAS WORKING ON, i WAS TOYING WITH =SUMIF(D:D, =E2, B:B) D:D BEING THE DATE IN NUMBER FORMAT IN A HIDDEN COLUMN E2 BEING TODAY()-180 IN NUMBER FORMAT B:B THE FLIGHT TIME ENTERED THE PROBLEM WITH THIS THOUGH IS THAT THE CRITERIA WILL NOT ALLOW A FIELD ADDRESS E2 IN CONJUCTION WITH = BUT IT WILL ALLOW THEM TO STAND ALONE. IT WORKS PERFECT IF I TYPE IN =38800 38800 IS (20 SEPT 2006) "Sandy Mann" wrote: Try: Day: =SUMPRODUCT(($A$2:$A$100=TODAY()-90)*(B2:B100)) Night: =SUMPRODUCT(($A$2:$A$100=TODAY()-90)*(C2:C100)) NVG: =SUMPRODUCT(($A$2:$A$100=TODAY()-90)*(D2:D100)) Adjust the range to suit. The absolute ranges ($A$2:$A$100) are so that I could copy the Day formula across the other two columns. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "KiowaPilot" wrote in message ... I am trying to keep a log of Flight time and would always like to know what I've flown within the last 90 days of the current date in several different modes of flight. A B C D 1 Date day night NVG 2 2 Sep 06 1.5 2.4 3 15 Sep 06 1.2 2.6 4 22 Sep 06 2.0 4.0 What is the formula to automatically calculate? |
All times are GMT +1. The time now is 09:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com