Beginer's problemBeginner's problem

M

Milan Karakas

Hi!

I begin to do math in Excel (2002), but at beginning stuck with luck of
knowledge, can anybody help me?

Simple formulas:

I want to calculate frequency given by formula: f=1/(2*pi*sqrt(L*C)),
where f=frequency, L=inductance, C=capacitance.

L is in cell A1,
C is in cell A2,
f is in cell A3

In cell A7, I put the formula:
[cell A7] =1/(2*PI()*SQRT(A1*A2))

The formula use only A1 and A2 value for calculating. This works,
because inductance and capacitance are known. In A7 it give me result.

But, what if I know only frequency, and capacitance, and want to know
(calculate) which inductance to use?

In cell A5 I wrote the formula:
[cell A5] =1/((2*PI())^2)*A3^2*A2)

The formula now use only A2 and A3 and put result in A5. This works as well.

Now, I want to know the capacitance from A1, and A3 and result is in A6:
[cell A6] =1/((2*PI())^2*A3^2*A1)
---

The problem:

How to write input in A1, A2 and A3 so that if one field is empty (for
example A1) not give me result in A5 and A6: [#DIV/0!] ?

When given result in cell A5 manually Copy/PasteSpecial (because
Copy/Paste "transfer" formula instead value) to A1, only then I get
right results in A6 and A7.

Is possible to do it automatically?

I can in cell A1 put =A5, but next time when I want to calculate
capacitance vs. frequency to give inductance, I need to write in field
A1 and A3 values - this attempt overwrite =A5, and then all results in
formulas A5, A6, and A7 are incorrect because in A2 is an "old" value.


What to do?

How to make each three calculation "independent", so that every time it
can calculate from only two inputs without errors?

And, how to "lock" formulas, so that not permit me accidentally
overwrite cells consisting formulas with some number?

Thank you in advance,

Milan Karakas
 
J

JE McGimpsey

One way:

A5: =IF(A3*A2=0, "", 1/((2*PI())^2)*A3^2*A2))
A6: =IF(A3*A1=0, "", 1/((2*PI())^2*A3^2*A1))
A7: =IF(A1*A2=0, "", 1/(2*PI()*SQRT(A1*A2)))


You could also use something like

A5: =IF(ISERROR(1/((2*PI())^2)*A3^2*A2),"",1/((2*PI())^2)*A3^2*A2)

but, for me, there are three disadvantages:

1) additional unnecessary calculations. This probably isn't a big deal
if your sheet isn't calculation intensive.

2) It masks other errors. If A1:A3 are always manually entered, this
isn't a big deal either, but if any of them are the result of
calculations, errors elsewhere in the sheet that cause or pass through
an error to A1:A3 will be silently ignored.

3) It's just a bit harder to figure out when you come back to it 6
months from now.
 
M

Milan Karakas

Hi!

And, thank yo again.
---
JE said:
One way:

A5: =IF(A3*A2=0, "", 1/((2*PI())^2)*A3^2*A2))
A6: =IF(A3*A1=0, "", 1/((2*PI())^2*A3^2*A1))
A7: =IF(A1*A2=0, "", 1/(2*PI()*SQRT(A1*A2)))
Click to expand...

Ah, now I see; this only mask error. When if A1 is zero, then only A5
give me result, and other cells are empty A6:A7 (instead errors). Now I
MUST manually Copy/PasteSpecial(value)from A5 to A1 to get other two
results. Is there a way to do it automatically?
You could also use something like

A5: =IF(ISERROR(1/((2*PI())^2)*A3^2*A2),"",1/((2*PI())^2)*A3^2*A2)
Click to expand...

This not work the best. I have also problem because instead "," I should
use ";" (Croatian version of Excel).
but, for me, there are three disadvantages:

1) additional unnecessary calculations. This probably isn't a big deal
if your sheet isn't calculation intensive.
Click to expand...

For now yes, but it will be intensive, because I think to add more
formulas connected to this values (for example; calculation of Q factor,
Impedance, losses...etc...).

One day, I will need the best Q factor (for example only), which will
put back value into A1, A2, or A3 from another formulas. Then I will be
in big trouble.

What is for now very important to me to know: how to back results from
formulas (A5:A6) to input cells (A1:A3) without using i.e. [A1 =B5] etc...
2) It masks other errors. If A1:A3 are always manually entered, this
isn't a big deal either, but if any of them are the result of
calculations, errors elsewhere in the sheet that cause or pass through
an error to A1:A3 will be silently ignored.
Click to expand...

You got my point; I want enter missing value automatically from result
of another cells (either A5:A6), but not know how. But, at this way that
it still permit me manually change values A1:A3.

Do I need some sort of Visual Basic programing, or just write formulas
correctly?
3) It's just a bit harder to figure out when you come back to it 6
months from now.
Click to expand...

You are so right. I will forget it after few days, no need wait 6
months. :)

Cheers,
Milan Karakas

---
Hi!

I begin to do math in Excel (2002), but at beginning stuck with luck of
knowledge, can anybody help me?

Simple formulas:

I want to calculate frequency given by formula: f=1/(2*pi*sqrt(L*C)),
where f=frequency, L=inductance, C=capacitance.

L is in cell A1,
C is in cell A2,
f is in cell A3

In cell A7, I put the formula:
[cell A7] =1/(2*PI()*SQRT(A1*A2))

The formula use only A1 and A2 value for calculating. This works,
because inductance and capacitance are known. In A7 it give me result.

But, what if I know only frequency, and capacitance, and want to know
(calculate) which inductance to use?

In cell A5 I wrote the formula:
[cell A5] =1/((2*PI())^2)*A3^2*A2)

The formula now use only A2 and A3 and put result in A5. This works as well.

Now, I want to know the capacitance from A1, and A3 and result is in A6:
[cell A6] =1/((2*PI())^2*A3^2*A1)
---

The problem:

How to write input in A1, A2 and A3 so that if one field is empty (for
example A1) not give me result in A5 and A6: [#DIV/0!] ?

When given result in cell A5 manually Copy/PasteSpecial (because
Copy/Paste "transfer" formula instead value) to A1, only then I get
right results in A6 and A7.

Is possible to do it automatically?

I can in cell A1 put =A5, but next time when I want to calculate
capacitance vs. frequency to give inductance, I need to write in field
A1 and A3 values - this attempt overwrite =A5, and then all results in
formulas A5, A6, and A7 are incorrect because in A2 is an "old" value.


What to do?

How to make each three calculation "independent", so that every time it
can calculate from only two inputs without errors?

And, how to "lock" formulas, so that not permit me accidentally
overwrite cells consisting formulas with some number?

Thank you in advance,

Milan Karakas
Click to expand...
Click to expand...
 
M

Milan Karakas

Problem solved!!!

Thank you all for spent your time.

Cheers,
milan

P.S. Still, I don't know how to protect, or lock formulas to prevent
accidentally overwriting.
 
G

Gord Dibben

Milan

By default all cells are locked when the sheet is protected.

Hit CRTL + A(twice if xl2003) then Format>Cells>Protection. Uncheck "locked"
and OK.

Select the cells you wish to lock and Format>Cells>Protection. Check "locked"
and OK

Now Tools>Protection>Protect Sheet. This is mandatory.

You can set a password to unprotect the sheet. These can easily be broken in
Excel but will keep your formulas from being accidentally overwritten.

NOTE the options available in xl2002 and 2003 when you hit Protect Sheet


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Highlight duplicate cells based on a selected cell 5
Copy a formula using Vlookup 2
How to use cell reference? 9
How to synchronize all cell values? 1
How to make Weighted Average 3
Alpha sorting (with a twist...?) 3
how to find the 2nd last value of a column 2
Copy By Variable 4

Top