row total - column total

Hi,

I am new to excel. Below is what I need. Is there any possibility that I write a formula for a column and it caliculates the particular rowtotal minus the particular column total for each cell in that column insted of writing formula for each cell.

Please Login or Register  to view this content.

If you observe first colun in last column in first row is the total row1-totalcolumn1, last column in second row is totalrow2-totalcolumn2 and same way is the third column. Is tehre any way to write a formula for the last column so that it automatically does the math for each cell of that column.
I can not write the formula for each cell as there is huge data.

Thanks in advance.

Originally Posted by musini

Hi,

I am new to excel. Below is what I need. Is there any possibility that I write a formula for a column and it caliculates the particular rowtotal minus the particular column total for each cell in that column insted of writing formula for each cell.

Please Login or Register  to view this content.

If you observe first colun in last column in first row is the total row1-totalcolumn1, last column in second row is totalrow2-totalcolumn2 and same way is the third column. Is tehre any way to write a formula for the last column so that it automatically does the math for each cell of that column.
I can not write the formula for each cell as there is huge data.

Thanks in advance.

Can I suggest a small change which helps immensely. And that's to put the column totals above the data rather than at the bottom. That way the total is always in the same row however many rows of data you have. Obviously make the total be =SUM(A2:A65536) or whatever. So with that small change, and with this test example, if the data is in B5:D6, with the totals on row 4, name the first cell of data, the one with the value '1' as "Start".

Now in F5 put the following formula:

Please Login or Register  to view this content.

and copy down as appropriate.

Rgds

As I have told you I am new, I di not understand the formula.

Please Login or Register  to view this content.

Could you please rewrite the formula for the cells according to the example. Thanks for the help.

Regards,
M

Originally Posted by musini

As I have told you I am new, I di not understand the formula.

Please Login or Register  to view this content.

Could you please rewrite the formula for the cells according to the example. Thanks for the help.

Regards,
M

Hi,

My formula was generalised so that it will apply whether you have the data in A1:C4 or BD987:BF1203. That's why I located the start of the data where I did.

However if it helps you further, and provided you've made the change with the total row - (as I said it's generally a good idea to have totals at the top of ranges that vary with the number of rows), put the following formula in F2.

Don't forget to give the name "Start" to cell B2. To do this go to the dropdown box in the Menu area, just above the 'A' of column A in the spreadsheet, and type in the name 'Start': The F2 formula is:

Please Login or Register  to view this content.

Thanks for the new formula. When you said sum(B2:D2). B2 is fine because it always starts with B2, but what bout D2 because it not gaurenteed that my columns end at D. Is there a way to make D2 more generalized.

Thanks,
M

Originally Posted by musini

Thanks for the new formula. When you said sum(B2:D2). B2 is fine because it always starts with B2, but what bout D2 because it not gaurenteed that my columns end at D. Is there a way to make D2 more generalized.

Thanks,
M

Hi,

Yes undoubtedly the formula could be extended to include some function that would work out which was your last column - however I think that would complicate it unnecessarily.

Provided you always insert new columns before column D, the SUM(B2:D2) range will change automatically to include the last column.

Rgds

That formula works fine, but for every column i have to change the b2,d2 to b3,d3 and so on..

you told to do

Please Login or Register  to view this content.

this, but how do i do it.

may be i am not able to understand how to make them change automatically.

Thanks,
M

Originally Posted by musini

That formula works fine, but for every column i have to change the b2,d2 to b3,d3 and so on..

you told to do

Please Login or Register  to view this content.

this, but how do i do it.

may be i am not able to understand how to make them change automatically.

Thanks,
M

Hi,

No, you'll find the references change automatically. So just copy F2, then paste it straight into the range F3:Frr , where rr is the last row number of your data.
F3 will then refer to B3:D3, F4 will refer to B4:D4 etc..

Afterwards, and to help your understanding, read up on the difference between Absolute formulae, of the form $B$2:$D$2, and Relative formulae with the form B2:D2

HTH