0

How to create a chart in Google Sheets for yearly value growth when the data is separated by quarters?

For enter image description here

enter image description here

1

Year name
=mid(OFFSET(A$1,4*ROWS(A$1:A1)-4,,1),4,4)

Value
=SUM(OFFSET(B$1,4*ROWS(B$1:B1)-4,,4))

Enter these formula in cell D1 and E1 and copy down for as many years data exists.

Logic
Both formula rely on the OFFSET and ROWS functions.

  • OFFSET returns a range reference
  • ROWS returns the number of rows in an range.

The Year value looks at Column A; it returns a single cell, every 4th entry, and MID extracts the year name. The Annual value looks at Column B; it returns a range of four cells, and adds those values.

Credit: myonlinetraininghub.com

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.