excelexcel-formula

Transpose values from row to column and calculate the difference between the values

0 A B C D E F G H I
1 Q1 Q2 Q3 Q4 Q5
2 Product 100 400 600 200 900
3 Product
4 Q1 100
5 Q2 300
6 Q3 200
7 Q4 -400
8 Q5 700

In Range H3:I8 I want to transpose the values from Range A1:F2.
For the transpose I use this formula TRANSPOSE(A1:F2).

However, I don't know how to include the calculation of the difference between the numbers in Range B2:F2 to the following transpose formula:

I4 = 100 - 0 = 100
I5 = 400-100 = 300
I6 = 600-400 = 200
I7 = 200-600 = -400
I8 = 900-200 = 700

Do you have any idea?

Note: There will be always exactly one row to transpose.

Solution

  • You may try-

    =TRANSPOSE(HSTACK(VSTACK("",A2),VSTACK(B1:F1,IFERROR(B2:F2-A2:E2,B2))))

    enter image description here