Help With Formula

[Frenchy]

I have a formula in a spreadsheet that works fine

=IF(C17=0," ",(C15^2*0.4465*C15/12*PI()*C17/5000)*3.785)

This returns the correct answer and if no entry in C17 is stays blank (as I want it to)

What I want it to do is return HALF the answer if there is an entry of a letter M or m in
say Cell C13?

So if nothing in C13 the normal answer appears and if an M in C13 then half of that answer

Many thanks
Frenchy

 

[John Wilson]

Frenchy,

Does this do what you want???

=IF(C17=0,"",IF(C13="m",((C15^2*0.4465*C15/12*PI()*C17/5000)*3.785)/2,(C15^2
*0.4465*C15/12*PI()*C17/5000)*3.785))

John

 

[Frenchy]

John, that is much appreciated!! Works exactly as I wanted it to! I had tried that but
didn't have the "" around the m.

Also appreciate your prompt reply!

Frenchy

 

[Dana DeLouis]

Just a slightly different way to this excellent idea for testing "m" or "M."

=IF(C17=0,"",(272305*C15^3*C17)/3077298651/IF(UPPER(C13)="M",2,1))

 

[Frenchy]

Dana, I should have found this news help group years ago <smile>

Your version works fine!

OK, yours is different looking and the constants have all been grouped together but I am not
sure what the ,2,1 at the end do?

Also UPPER should return Upper case of text, right? Yet if you put "m" in C13 it shows as
"m" not "M". This doesn't matter, but just for my learning curve??

Thanks

Frenchy

 

[Bob Phillips]

Frenchy,

What this does is that instead of replicating the formula whether C13 has an
M or not, it simply divides the formula result by 1 or depending upon
whether C13 has an M, So it says

IF(UPPER(C13)="M",2,1)

which resolves to 2 or 1 depending on whether C13 is M or m or not. Your
original form ula is then divided by this result

=formula / (IF(UPPER(C13)="M",2,1))

The UPPER doesn't return a upper case M, it just upshifts it within the
formula, for the purposes of the test.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

Here's a couple of explanations on your queries ..

..... IF(UPPER(C13)="M",2,1).....

but I am not
sure what the ,2,1 at the end do?

The 2,1 in the IF() above are the value_if_true and value_if_false
to be returned depending on the result of the logical test of UPPER(C13)="M"

"2" will be returned if UPPER(C13)="M" evaluates to TRUE
"1" will be returned if UPPER(C13)="M" evaluates to FALSE

Yet if you put "m" in C13 it shows as
"m" not "M".

As C13 is an input to the IF() formula,
the value in C13 will be simply what is entered in the cell.

If you want to see where the "UPPER(C13)" takes effect,
(assuming you have input "m" in C13)

try selecting *just* this part of the formula in the formula bar
(i.e. select "UPPER(C13)" with steady mouse control..)

and then press F9 to calculate

You should see "M" appear as the result in the "UPPER(C13)" selection
(this shows the function's conversion of the lowercase "m" to uppercase "M")

Press Esc to cancel

 

[John Wilson]

Dana,

Nice way to make that more concise.
I purposely left out the "Upper" statement
in my original reply as it didn't seem to make a difference.
Using your formula without the "Upper":
=IF(C17=0,"",(272305*C15^3*C17)/3077298651/IF(C13="M",2,1))
works whether C13 = "m" or "M" ?????

Using Excel 2000/Win-XP

John

 

[Peo Sjoblom]

Correct, like most functions IF is not case sensitive.
FIND and SUBSTITUTE are case sensitive.

 

[John Wilson]

Peo,

Thanks for the clarification.

John

Correct, like most functions IF is not case sensitive.
FIND and SUBSTITUTE are case sensitive.

--

Regards,

Peo Sjoblom


=IF(C17=0,"",IF(C13="m",((C15^2*0.4465*C15/12*PI()*C17/5000)*3.785)/2,(C15^2