I have a table in Mac numbers software that is consist of the following:
• column A ( Date )
• column B ( details)
• column C and D ( type of fuel gasoline/ diesel)
• column E ( debit amount)
• column F ( Credit amount)
• column G ( Balance)
I want to create another table that is contains in column (A) months of the year and column (B) the balance at the end of each month.
How can I do that in Mac numbers?
MacBook Air 13″, macOS 12.7
A Pivot Table can sum or count by month (very handy). But is not easy (if indeed that is possible) to configure a Pivot Table to extract the last value in each month.
If you are using Numbers 14.4, you can, however use XLOOKUP, something like this:
In B2, filled down:
=XLOOKUP(MONTH(A2),MONTH(Table 1::A),Table 1::G,if-not-found,match-type,-1)
This looks up the last value for each month in column G.
Use ; instead of , in the formula if your region uses , as the decimal separator.
SG
You could create a pivot table summarizing your data by month: Create a pivot table in Numbers on Mac - Apple Support.
End of months balance value in Mac numbers
