RE: Calculation using IF

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Without quotes around your city names, XL thinks you are referrning to named
ranges. With no named range in your sheet of those names (I presume) XL
concludes that C4 does not equal them. Since all your conditions then become
false (and FALSE = 0) your equation performs the calculation of 0 * D4 = 0.
Just switch the quotes around:

=IF(C4="Philadelphia",0.5,IF(C4="Baltimore",0.75,IF(C4="Washington",1)))*D4

If you do plan on using more cities, I'd agree with Bill, it'd be easier to
update using a vlookup table to return your multiplication factor.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bill R" wrote:

I want to use the IF function to perform a simple calculation. In cell C4 I
would enter one of three place names. In cell F4 I'd like the formula to use
a numeric value depending on the place name in C4. Then I'd like to use the
numeric value to perform a calculation involving other cells e.g.

If I enter Philadelphia into C4 Excel 2003 in F4 would use the value 0.50 to
multiply by a volume entered into D4, say 75000.
If ,however, I enter Baltimore into C4 the value to be used in F4 would be
0.75 then multiply that by D4 -75000.
If I enter Washington into C4 the F4 value used would be 1.00.

=IF(C4=Philadelphia,"0.50",IF(C4=Baltimore,"0.75",IF(C4=Washington,"1.00")))*D4

At the moment I'm getting an answer of 0 instead of 37,500 for Philadelphia;
56,250 for Baltimore and 75,000 for Washington.
The people using this spreadsheet should be able to enter a destination and
a volume to arrive at an overall rate.

--
Bill R
.

Relevant Pages

  • Re: How to find a quote in a string
    ... Bill H wrote: ... I trying to code if a line has double quotes around it Then ... If the search failed, strposreturns boolean FALSE, as opposed to 0 or an empty string. ... $pos will hold 0, of type integer. ...
    (comp.lang.php)
  • RE: Accessing a macro created named range
    ... with your code if you go to name manager and select the name then in the ... Refers to field you will see double quotes around the sheet name and range ... The code that I have given you will create the named range correctly without ... Syntax for naming the range is incorrect. ...
    (microsoft.public.excel.programming)
  • Re: Paging the building sheddi
    ... had got for our extension, and the bill he submitted based on a ... percentage of those quotes when it was clear that we no longer wanted to ... That was just the labour - materials extra. ...
    (uk.rec.sheds)
  • Re: No, Joe, *YOU* Lie!!!!!
    ... And do you still insist that a fact taken from the bill isn't a fact if ... Media Matters quotes it? ... but even a blind squirrel finds an acorn once in a while. ...
    (rec.sport.golf)
  • Re: messages from the values voters
    ... I thought he was no longer a US Senator? ... your link did not explain why he likes the bill. ... "arthur wouk" wrote in message ... "Quotes of the day " ...
    (soc.retirement)