Easy question??

[JaredMcCullough]

I just have a general problem i am trying to simplify my spreadsheet by making corresponding rectangles lets say cell A1 contains two values one a high and one a low (11,12) is it possible for me to average these two numbers and have that average in C1. To continue then B1 would be (15,16). And i would want D1 to be the average of these numbers.
A2 would be (11,22) and I want C2 to be the average of these two. It may be something simple but i have become completely dumbfounded by it thanks

 

[Jonmo1]

Try this with a slight modification to the UDF supplied earlier by Rick

Function Maths(MyVal As String) As Double
  Maths = Evaluate("=" & MyVal)
End Function

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 28px"><COL style="WIDTH: 15px"><COL style="WIDTH: 21px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid">A</TD><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid">B</TD><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid">C</TD><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid">D</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>*</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">288</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">88</TD><TD>/</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">9</TD><TD>+</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">122</TD><TD>-</TD><TD style="TEXT-ALIGN: right">75</TD><TD style="TEXT-ALIGN: right">47</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>=maths(A1&B1&C1)</TD></TR><TR><TD>D2</TD><TD>=maths(A2&B2&C2)</TD></TR><TR><TD>D3</TD><TD>=maths(A3&B3&C3)</TD></TR><TR><TD>D4</TD><TD>=maths(A4&B4&C4)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[wigi]

I think that if someone sees 14, 2 and 28, they will come to the conclusion that it's not the sum but the product...

In addition, formatting your cells in a coherent and consistent yet clear way, will also help a great deal.

 

[JaredMcCullough]

Still little confused i only have two cells essentially to work with all i was asking if there was a command or function that i could mutiple two numbers from the same cell and put the answer in the next cell or even the same cell if not thats quite alright i will find an alternative

 

[Jonmo1]

The code given in post #31 will do that...

That example used 3 cells (A1&B1&C1) to build a string creating a formula
=maths(A1&B1&C1)
A1 = 2
B1 = *
C1 = 4

If you want the formula string in 1 single cell, try
=maths(A1)

A1 = 2*4

 

[JaredMcCullough]

wow thank you very much it worked perfectly......now wondering if you

can include units lets say 12ft*2ft for A1 and then B1 would equal 24 ft2

can i represent that even with out the feet in the 24 i could just put that

in after i was just looking for a command with the units in that would allow

the next cell to equal the product of them if not thats quite alright this

has been helpful enough

 

[Jonmo1]

Math operations can only be performed on numerical characters (and operators like + - / *), not Alpha characters.

 

[JaredMcCullough]

ok thank you very much.... I have run into a slight dilemma most of my

tables are six by six so lets say the high low values are from A1 by A6 to

by F6 thus making a square skip a space to show spacing and the

table showing the averages is from H1 by H6 to M1 by M6 thus making to

tables beside each other well I have one table that is an eight by eight

Meaning its averaged value from A would not be in column H but instead J

but when I go to get the averaged value from A in J its actually giving me

C thats by dragging it and when i type the formula in it just doesnt work

at all

 

[JaredMcCullough]

Somehow i was able to solve the problem on my own hopefully i will not have to bother you guys again but i appreciate all the help

 

[Jonmo1]

Glad to help, thanks for the feedback...

Next time, maybe you'll think twice before giving your thread a title like
"Easy Question"

Just kidding...

 

[wigi]

Next time, maybe you'll think twice before giving your thread a title like
"Easy Question"

Just kidding...

You're right, Jonmo, and I'm not kidding. Topic titles like these are worthless for searching purposes (yet there ARE interesting functions and approaches covered in this topic).