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

To avoid the DIV/0 error format your formula like this: =IF(ISERROR([your
formula]),"",[your formula])

Dave
--
Brevity is the soul of wit.


"Milan Karakas" wrote:

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

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.




In article ,
Milan Karakas wrote:

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

Thanks Dave, this solve one of my problems.

---

Dave F wrote:
To avoid the DIV/0 error format your formula like this: =IF(ISERROR([your
formula]),"",[your formula])

Dave

Hi!

And, thank yo again.
---
JE McGimpsey wrote:

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)))

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)

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.

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.

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.

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

Cheers,
Milan Karakas

---




In article ,
Milan Karakas wrote:


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

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.

Milan

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

Hit CRTL + A(twice if xl2003) then FormatCellsProtection. Uncheck "locked"
and OK.

Select the cells you wish to lock and FormatCellsProtection. Check "locked"
and OK

Now ToolsProtectionProtect 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


On Wed, 24 Jan 2007 02:09:44 +0100, Milan Karakas wrote:

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

Thank you Gord, it works!

Cheers,
Milan Karakas

---

Gord Dibben wrote:
Milan

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

Hit CRTL + A(twice if xl2003) then FormatCellsProtection. Uncheck "locked"
and OK.

Select the cells you wish to lock and FormatCellsProtection. Check "locked"
and OK

Now ToolsProtectionProtect 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


On Wed, 24 Jan 2007 02:09:44 +0100, Milan Karakas wrote:


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