Numbers - Copy and Pasting formula correctly from a row to column table

Hi there,

My question is a bit tricky for me to put into words. Basically, I want to calculate average daily expenses of below month using formula: =B2÷DATEDIF($B1,$C1,"D").

I want to have my results into verticle table. But whenever I copy and paste formula into next cell into verticle table, instead of taking the next figure from C2, it takes a figure from B3 ie. from the same column instead of next cell in a row. (B3÷DATEDIF($C1,$D1,"D") )

I have to manually change everytime. However, if I create a horizontal table the formula changes correctly and takes C2. Anyway to resolve this?

Posted on Sep 17, 2018 12:57 AM

Reply

Top-ranking reply

Level 7

21,939 points

Posted on Sep 17, 2018 11:33 AM

Hi George,

Please tell us which version of Numbers, which device (Mac or an iDevice) and the version of the operating system.

However, here we go.

Tested in Numbers for Mac version 5.1 under MacOS Sierra.

Numbers > Menu > Table > Transpose Rows and Columns may be useful.

(I can not remember when Transpose Rows and Columns was introduced into Numbers.)

Duplicate your Expenses table.

Transpose Expenses-1 and rename it as Expenses Transposed (or some other meaningful name).

Add two extra columns to the right.

Formula in C2 (and Fill Down)

=DATEDIF($A2,$A3,"D")

Formula in D2 (and Fill Down)

=B2÷C2

The red error triangles occur because we do not yet have data for May 2018.

Regards,

Ian.

View in context

Similar questions

3 replies

Top-ranking reply

Level 7

21,939 points

Sep 17, 2018 11:33 AM in response to CuriousGeorge101

Hi George,

Please tell us which version of Numbers, which device (Mac or an iDevice) and the version of the operating system.

However, here we go.

Tested in Numbers for Mac version 5.1 under MacOS Sierra.

Numbers > Menu > Table > Transpose Rows and Columns may be useful.

(I can not remember when Transpose Rows and Columns was introduced into Numbers.)

Duplicate your Expenses table.

Transpose Expenses-1 and rename it as Expenses Transposed (or some other meaningful name).

Add two extra columns to the right.

Formula in C2 (and Fill Down)

=DATEDIF($A2,$A3,"D")

Formula in D2 (and Fill Down)

=B2÷C2

The red error triangles occur because we do not yet have data for May 2018.

Regards,

Ian.

Reply

Level 7

21,939 points

Sep 18, 2018 4:49 AM in response to CuriousGeorge101

Hi George,

But you are transposing a table 😕.

Here is a way to transpose a duplicate table without transposing a table 👿.

Formula in A1 (and Fill Right and Fill Down

=OFFSET(Expenses::$A$1,COLUMN()−1,ROW()−1)

Acknowledgement to Wayne Contello for this formula.

Regards,

Ian.

Reply

Sep 17, 2018 11:33 AM in response to Yellowbox

Hi Ian,

Thank you for your response. It does help. However, is it not possible to do the same without Transposing the table?

I am using Mac Sierra number version 5.1

Regards,

Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Numbers - Copy and Pasting formula correctly from a row to column table

Welcome to Apple Support Community

A forum where Apple customers help each other with their products. Get started with your Apple Account.

Learn more Sign up