Ask Your Question
0

How to update absolute cell number [closed]

asked 2020-04-02 16:12:06 +0200

Math3333 gravatar image

updated 2020-04-02 16:12:53 +0200

I have the following formula in a cell

=(E2-(SUMPRODUCT(WEEKDAY(G2:G$31)=1)+SUMPRODUCT(WEEKDAY(G2:G$31)=5)))

Now, depending on a month G$31 will change, that is the last two digits will change. How to add an integer to the cell address? to get from G$31 --> G$29 for example. I tried substructionG$(31 -2) but it did not work.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2020-04-02 21:09:15.221325

1 Answer

Sort by » oldest newest most voted
0

answered 2020-04-02 18:33:05 +0200

erAck gravatar image

updated 2020-04-02 18:34:53 +0200

OFFSET(G$31;-2;0)

So probably

=(E2-(SUMPRODUCT(WEEKDAY(G2:OFFSET(G$31;-2;0))=1)+SUMPRODUCT(WEEKDAY(G2:OFFSET(G$31;-2;0))=5)))

if I understood correctly.

edit flag offensive delete link more

Comments

@erAck, yes this works! Thank you. I can't upvote, don't have yet 5 points.

Math3333 gravatar imageMath3333 ( 2020-04-02 18:45:07 +0200 )edit

Marking the answer as correct with the check mark is fine, thanks!

erAck gravatar imageerAck ( 2020-04-02 21:09:01 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2020-04-02 16:12:06 +0200

Seen: 45 times

Last updated: Apr 02 '20