excel formula?

st999

In  F400 I have A365-A1 and in G400 I have B365-B1 and in F401 I have A366-A2 and in G401 I have B366-B2 and fill columns F and G down as I add to column A and column B.
Occasionally the result in column F is out of sequence so I have to manually adjust F410 to A362-A3  so therefore G410 becomes B362-B3.

Is it possible to have a formula in column G so that when I change the value in say F410 to A362-A3 G410 become B362-B3 without me having to fill across?

tacpot12

Sorry, I don't understand what you mean by "fill columns F and G down as I add to column A and column B." 
If you can explain what you are doing, and what results you are seeing, someone may be able to help you. 

a

A picture paints a thousand words - or in your case a screen shot

WaywardDriver

st999 said:

In  F400 I have A365-A1 and in G400 I have B365-B1 and in F401 I have A366-A2 and in G401 I have B366-B2 and fill columns F and G down as I add to column A and column B.
Occasionally the result in column F is out of sequence so I have to manually adjust F410 to A362-A3  so therefore G410 becomes B362-B3.

Is it possible to have a formula in column G so that when I change the value in say F410 to A362-A3 G410 become B362-B3 without me having to fill across?

If I've interpreted this correctly, F410 would normally be A374-A10 but needs to be changed to A362-A3. Does this mean F411 should be A363-A4 or does it revert to the original sequence as A375-A11?

Heedtheadvice

...and one may ask why the sequence needs to change as sorting that might be the easiest solution....always a bit of de Bono and lateral thinking!

st999

I fill in the date in column A and a number in column B and since the appropriate formulas are already in column F and G I fill down these columns.

Column A is the date, going back to 2000, there could be gaps between the dates such as 1/1/2000, 3/1/2000, 21/1/2000, 3/5/2000, then 1/2/2001, 3/1/2001, 23/1/2001, 23/2/2003.

Column B contains a number that always increases such as 12365, 13569, etc.

Column F subtracts the dates to give a result approx. 365 days later and column G subtracts the numbers in the same rows to give a usage figure between the two equivalent dates.

Because the spreadsheet isn't filled out every day, as time goes on the dates become out of sync so when column F is filled down there could be three dates in sequence such as  A900 1/3/2018, A901 2/3/2018, and A902 3/3/18.but a year later A1031 would be 1/3/2019 A1032 2/3/2019 and A1033 5/3/2020 giving in F1031 365 F1032 365 but F1033 would be 733.

To make F1033 more like a year I would change F1033 to be A1033-A1032

In my example this does not work but it is only an example.

Therefore I want G1033 to change automatically to B1033-B1032 so that I would only need to change the values in column F.

WaywardDriver

Similar to spreadsheet I set up for solar panel readings taken at irregular intervals and calculating a running yearly production.
In F400 enter =A400-VLOOKUP(A400-365,A$1:A400,1,TRUE) to return the number of days between the current date and the date approx 365 days ago for which there's a reading.
In G400 enter =B400-VLOOKUP(A400-365,A$1:B400,2,TRUE) for the increase in the reading over the approx 365 days.
Actually column F is redundant unless you want to adjust for there not being exactly 365 days between the dates. e.g. in G400 enter  =365*(B400-VLOOKUP(A400-365,A$1:B400,2,TRUE))/F400.
Cells F400, G400 should then copy down without any need for manual adjustment. 

st999

Thanks WaywardDriver that does what I want