Trying to track weight loss over 8 cells in the same row with the last cell showing the weight gain or loss

[Fiftyfingers]

i have a formula that works for 3 weight entries {=IF($AP5="",$AG5-$Y5,$AP5-$Y5)} Y= first weight entry, AG5= second weight entry and AP5= third weight entry. I am not sure how to expand it to figure weight gain or loss for seven entries.

All help is always appreciated.

 

[ace19852]

Hi and welcome to the board, I'm not clear what you really want my best guess is if in

Y5 = Start weight
AG5 = 1st weeks weight

now lets say the next 7 cell are filled in weekly with the new weight

AH5 = Week2 weight
AI = Week3 weight
AJ = Week4 weight
AK = Week5 weight
AL = Week6 weight
AM = Week7 weight
AN = Week8 weight

So in AO5

if you put

=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()-9+COUNT($AG$5:$AN$5),4),"1","")&ROW())-Y5

Then the new weight losses will update in AO5 as you filling in the weeks

Hope that is helpful

Cheers

 

[ace19852]

Or as an alternative to

=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()-9+COUNT($AG$5:$AN$5),4),"1","")&ROW())-Y5

I could have used in this case

=OFFSET(AO5,0,-9+COUNT($AG$5:$AN$5))-Y5

Which is probably better

Cheers

 

[Fiftyfingers]

Thank you for your help. i will try your suggestions and let you know.