Calculation using IF

[Bill R]

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.

 

[Bob Bridges]

The way I'd do this, rather than using IF, is to put each city and the
corresponding value for it in a separate table, say on a different worksheet.
That means you can refer to the table from as many rows as you like in your
home sheet, and also that you can easily update the table just once instead
of many times on the home sheet. VLOOKUP is the ticket for this method.

But if you really like IF functions, the only thing I see wrong with your
example is that you don't have quotes around the city names and do around the
values, just the opposite of what I think should be. Putting quotes around
the values makes them string values instead of numers, you see, and
apparently Excel thinks that multiplying a character string times D4 yields 0
as the proper answer.

 

[Luke M]

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.

 

[Bill R]

Thank you both Bob Bridges and Luke M. Your response did answer my query on
the IF function and I did indeeed have the quotation marks reversed but now
I'm intrigued by the VLOOKUP function you mention. If instead of one rate for
each city for the volume to be transported I actaully have different rates
would that be the way to go still?

In other words I have a rate of 0.50 for Philadelphia for 70000 to 79000 but
for 80000 to 89000 the rate is 0.4 and for 90000 to 10000 the rate is 0.30. I
have similar value changes for Washington and Baltimore as the volume range
changes.

 

[Luke M]

Say you setup a table like this in columns G:I. Note that I'm placigin the
lower limit for each range:
City Bottom Limit Factor
Philadelphia 60000 0.5
Philadelphia 70000 0.4
Philadelphia 80000 0.3
Baltimore 60000 0.75
Baltimore 70000 0.65
Baltimore 80000 0.55

Because you are needing multiple criteria lookup, you need SUMPRODUCT
=SUMPRODUCT((G2:G7=C4)*(D4-H2:H7<10000)*(D4>=H2:H7)*(I2:I7))*D4

Obviously, you will need to adjust references and range sizes to match your
data.

 

[Bob Bridges]

I never learned how to use the matrix functions, SUMPRODUCT and so forth, so
I can't comment on Luke's solution. Mine would be a two-dimensional table,
like this:

70,000 80,000 90,000
Baltimore 0.75 0.65 0.45
Philadelphia 0.5 0.4 0.3
Washington 1.0 0.8 0.6

That first row shows the volume limits, of course; your formula on the home
sheet should take the volume figure for the order and use MATCH to look up
the correct column across the top of your city-and-volume table. Then use
VLOOKUP to look down the list of cities for an exact match and return the
rate from the column you got back from MATCH.

There are minor wrinkles; for example, MATCH returns a relative column
number, ie relative to the table, so you get back 1, 2 or 3 but you want to
tell VLOOKUP column number 2, 3 or 4. That's easy to iron out; you just add
one to the MATCH results. If you want to try it and aren't sure how to
start, post here or email me at the address attached to my profile and I can
walk you through with samples. But the final formula might look like this:

=VLOOKUP(C4,CityVol!A,MATCH(D4,CityVol!A2:A4,1)+1,FALSE)

 

[Shane Devenshire]

Hi,

You already have explainations about why it didn't work and you have a few
basic solutions for this problem. This is another solution

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

If this helps, please click the Yes button.

Cheers,
Shane Devenshire