Horizontal formulas?

  • Thread starter Thread starter Melissa in Salem
  • Start date Start date
M

Melissa in Salem

I know how to create formulas for calculations as long as the numbers
are stacked vertically, like adding top to bottom.

How do make formulas that go across the page horizontally? Is that
possible? I am sure it is but I am stumped.
 
hi
there is no real hocus pocus stuff involved. it just how you wright the
formula
vertical......
=sum(A1:A20) 'note: all column A
horizontal......
=sum(A1:R1) 'note: All row 1
diagonal.....
=sum(A1,B2,C32,D4,E5) note: 45 degree down from A1
sporatic....
=sum(R20,V2,AB599,B1,U2,X65000) note: uh...all over the place

any other way you want it?

Regards
FSt1
 
Melissa

Assuming I have understood you correctly, you do it exactly the same
way.

So for example, to sum a list of numbers going horizontally in columns
A to F, row 1, and put the result in G1, then in G1 type =sum(A1:F1)

For doing things horizontally the letters change rather than the
numbers, as they do if you are calculating vertically.

Regards

Murray
 
Melissa

Assuming I have understood you correctly, you do it exactly the same
way.

So for example, to sum a list of numbers going horizontally in columns
A to F, row 1, and put the result in G1, then in G1 type =sum(A1:F1)

For doing things horizontally the letters change rather than the
numbers, as they do if you are calculating vertically.

Regards

Murray
Click to expand...

Wow, that was so easy. Blush. Thanks 1,000,000.
 
Melissa

Assuming I have understood you correctly, you do it exactly the same
way.

So for example, to sum a list of numbers going horizontally in columns
A to F, row 1, and put the result in G1, then in G1 type =sum(A1:F1)

For doing things horizontally the letters change rather than the
numbers, as they do if you are calculating vertically.

Regards

Murray
Click to expand...

Hi again Murray,

OK, here is another try, same thing. For example, adding numbers in a
vertical fashion, the formula covers a range of cells (=SUM(A1:A30)
-- that's a vertical stack of numbers, right?

Now a horizontal example: I want to multiply whatever the number is
in col A1 by 7 and have it show up in B1 (B1 = A1 x 7). And I want it
set up so that, like, in A1:A30 each number in Col A numbers get
multiplied by 7 and that figure shows up next to it in column B. So
it's multiplying across. Is that explained well enough? B1 = A1 x
7.

Is there a formula for that and where does it go? It seems to me I
would have to enter the formula each time. Am I right or wrong?
 
Melissa, besides these comments, look at Excel's HELP on the subjects of
absolute and relative addressing, and on Fill.

You have values in A1:A30 and want to multiply each by 7 with result showing
up in column B. So in B1 you'd put the formula
=A1 * 7
and "fill" that formula down to row 30. Excel will automatically change the
row number, so in B2 the formula will become =A2 * 7, and so on down to row
30 where the formula will have become =A30 * 7

Now if you wanted to do the multiplication on the values in column A and
have them show up in row 1 from B1 across to AE1, you could do it with a
formula like this in B1 that you would fill across to AE1:
=OFFSET($A$1,COLUMN(B1)-2,0) * 7
What that says is: using cell A1 as the base location, choose a cell that is
offset from it by the number of rows calculated by taking the column number
the formula is in and subtracting 2 from that, and without any column offset
and multiply that cell's value by 7.
When the formula is in column B, the "COLUMN(B1)-2" value is zero, but when
it is filled to column C, that becomes "COLUMN(C1)-2" which is 3-2 = 1. So
it is working down the rows in column A. Hope this all helps some.

As I said, use Excel's Help on Absolute and Relative addressing to help
understand how the $ plays into effect in cell addresses. The help on Fill
will tell you how to fill the formulas I've provided down/across the sheet.
 
Back
Top