Excel, proper equation format.

In summary, the conversation discusses using Excel to create a program that calculates a ratio and plot it using given values of R and λ. The formula is broken down into smaller parts to make it easier to troubleshoot, and simple operators like ^, *, and / are recommended for use. There is some confusion over the correct form of the equation, but eventually, the simpler functions are used and the program is successfully created. The conversation also touches on the use of algebra and paper to simplify equations before entering them into a computer program.
  • #1
ricky12tt
5
0
Ok, so the teacher gave us an assignment to write a program that calculates the ratio below, tabulate it and plot it.. for given values of R and λ.

T_b/T_a =([R^4-(R-λR)^4])/([4λR^2 (R-λR/2)^2])

I figured that I would use Excel to do this.

I think the above equation converts to the function below in excel but I keep getting zero for all values I try.

C2=R
And A4=λ

=QUOTIENT(((POWER(C2,4))-(POWER((C2)-(PRODUCT(C2,A4)),4))),(PRODUCT((POWER(((C2)-(QUOTIENT((PRODUCT(A4,C2)),2))),2)),4,A4,(POWER(C2,2)))))


Any help would be appreciated.
Thanks
 
Technology news on Phys.org
  • #2
I would break it up into manageable chunks putting them in a series of cells. Then combining the pieces into the final relationship.

Also you can use simple operators like ^ for exponentiation, * for multiplication and / for divide. That may be easier to read and troubleshoot then the functions you are using.

Is that ratio correct? Looks to me like R cancels out.
 
  • #3
Well... my excel does not understand "QUOTIENT" (I have an English speaking Excel 2003).
And also, there seems a mistake in your formula: ([R^4-(R-λR)^4])/([4λR^2 (R-λR/2)^2]) - the underlined part, should I subtract one part from the other, or multiply? There's a symbol missing. I chose for subtracting.

Then:
T_b/T_a =([R^4-(R-λR)^4])/([4λR^2 - (R-λR/2)^2])
T_b = ([R^4-(R-λR)^4])
T_a = ([4λR^2 - (R-λR/2)^2])

And in Excel language:
(For T_b)
=((C2^4)-(C2-($A$4*C2)^4))
(For T_a)
=((4*$A$4*C2^2)-(C2-($A$4*C2/2)^2))
 
  • #4
I'm sorry.. it supposed to be multiplied
([R^4-(R-λR)^4])/([(4λR^2)*(R-λR/2)^2])

I'll try your suggestion. I have Excel 2007 though.
I think Initially your method is what I had tried but I'll give it a go again.




CaptainPanic said:
Well... my excel does not understand "QUOTIENT" (I have an English speaking Excel 2003).
And also, there seems a mistake in your formula: ([R^4-(R-λR)^4])/([4λR^2 (R-λR/2)^2]) - the underlined part, should I subtract one part from the other, or multiply? There's a symbol missing. I chose for subtracting.

Then:
T_b/T_a =([R^4-(R-λR)^4])/([4λR^2 - (R-λR/2)^2])
T_b = ([R^4-(R-λR)^4])
T_a = ([4λR^2 - (R-λR/2)^2])

And in Excel language:
(For T_b)
=((C2^4)-(C2-($A$4*C2)^4))
(For T_a)
=((4*$A$4*C2^2)-(C2-($A$4*C2/2)^2))
 
  • #5
I would try that. They probably do cancel out but the arithmetic was taking me too long.


Integral said:
I would break it up into manageable chunks putting them in a series of cells. Then combining the pieces into the final relationship.

Also you can use simple operators like ^ for exponentiation, * for multiplication and / for divide. That may be easier to read and troubleshoot then the functions you are using.

Is that ratio correct? Looks to me like R cancels out.
 
  • #6
If you are unable to do the simple algebra required to cancel out the R, how do you hope to get the formula entered correctly into Excel?

Do the Algebra, it will simplify the spread sheet.

Also note that double posting is strictly against fourm policy.
 
  • #7
I didn't bother to do any algebra... but indeed, if it is important, writing out an equation on paper before entering it into the computer generally makes life easier. Equations in a computer program can become a nightmare with all the brackets and pluses/minuses.

I'd be surprised if Excel 2003 "language" does not work in 2007. If that would be true, it would be a very strong argument for Matlab or Octave (Octave is the GPL version of Matlab, with a lot less functionality, but more than adequate to plot this little equation).
 
  • #8
Hey Thanks. I used the simpler functions and they worked.
 
  • #9
I got it working.
Thanks for all the help

CaptainPanic said:
I didn't bother to do any algebra... but indeed, if it is important, writing out an equation on paper before entering it into the computer generally makes life easier. Equations in a computer program can become a nightmare with all the brackets and pluses/minuses.

I'd be surprised if Excel 2003 "language" does not work in 2007. If that would be true, it would be a very strong argument for Matlab or Octave (Octave is the GPL version of Matlab, with a lot less functionality, but more than adequate to plot this little equation).
 

1. What is the proper equation format in Excel?

The proper equation format in Excel is to use the equal sign (=) at the beginning of the equation, followed by the cell references or values, and then the mathematical operators (+, -, *, /) to perform the desired calculation. For example, "=A1+B1" would add the values in cells A1 and B1.

2. How do I create a complex equation in Excel?

To create a complex equation in Excel, you can use parentheses to specify the order of operations, as well as functions and named ranges to simplify the equation. You can also use cell references to make the equation dynamic and easily changeable.

3. Can I use Excel to solve equations?

Yes, Excel has built-in functions, such as SUM, AVERAGE, and MAX, that can solve equations and perform calculations on a range of cells. You can also use the Goal Seek feature to find the value of a cell that will make a specific equation or formula equal to a desired result.

4. How do I format equations in Excel?

To format equations in Excel, you can use the Format Cells feature to change the font, style, and color of the equation. You can also use the Cell Styles function to quickly apply pre-defined formatting to your equations.

5. Why is it important to use proper equation format in Excel?

Using proper equation format in Excel ensures that your equations are correctly interpreted and calculated. It also makes it easier to read and understand your equations, especially when working with complex calculations or sharing your spreadsheet with others.

Similar threads

  • MATLAB, Maple, Mathematica, LaTeX
Replies
2
Views
200
Replies
3
Views
2K
  • Special and General Relativity
3
Replies
75
Views
3K
Replies
2
Views
503
  • Programming and Computer Science
Replies
13
Views
3K
Replies
3
Views
1K
  • Advanced Physics Homework Help
Replies
8
Views
7K
  • Programming and Computer Science
Replies
2
Views
772
  • Precalculus Mathematics Homework Help
Replies
8
Views
960
  • MATLAB, Maple, Mathematica, LaTeX
Replies
7
Views
3K
Back
Top