EXCEL Formula Help

[zteam777]

I am trying to enter the following formula but I am coming up blank.

Ok, I have excel 2007.

I created a purchase order. On the purchase order I am trying to calculate multiple sales tax possibilities depending on which state I place in the State cell.

The 3 cells I am working with are: State "C5", Sub Total "D5", and Sales Tax "E5".

When I place "CA" in "C5" I want "E5" to calculate "D5" * 9.75%,
when I place "LA" in "C5" I want "E5" to calculate "D5" * 8.75%,
and when I place any other initials besides those 2 above in "C5" I want "E5" to calculate "D5" * 4.00%

Can someone please walk me through that particular formula?

Thank You

[Bob_180_Bob]

Hi,
Excel or mathematics have never been my forte but give this site a look to see if will help you.
http://www.mrexcel.com/archive/Formulas/index.html

[kaninelupus]

Not an expert in Excel, but still have to admit you may not actually be able to achieve what you are after, by the means you are trying to achieve. Let me explain.

To achieve your ends, you would need to assign values to "LA" and "DC", likely though a separate table/index; spreadsheet would then need the ability to call-up said values to align into the equation. To be honest, I'm not sure this is within the realm of Excel's capabilities.

You may be better off taking an easier approach, for example:
Column "D" would multiply a value in the corresponding Cell in column "C" by 109.75. Column "F" would multiply a value added to column "E" by 108.75. You would simply add the retail price (minus tax) to the appropriate column.... not the neatest method, but should work.

Either that, or opt for a proper point-of-sale program which should actually have the functionality you are currently trying to achieve in Excel :)

[cole davidson]

Excel does not have the features for what you are trying to do. You can however create two different purchase orders using two Excel sheets, one for each state.

[vmanes]

Oh, it's not really that hard. You could do this if you were writing it in program code, and it's just a little jump to do it in Excel.

A cascading if...else block does it.

=IF(C5="CA",D5*0.0975,IF(C5="LA",D5*0.0875,D5*0.04))

[kaninelupus]

Oh, it's not really that hard. You could do this if you were writing it in program code, and it's just a little jump to do it in Excel.

A cascading if...else block does it.

=IF(C5="CA",D5*0.0975,IF(C5="LA",D5*0.0875,D5*0.04))

Nice, but in that format, wouldn't that algorithm need to be applied an a row-by-row basis? Can the current "D5" be adapted using a wildcard, so that the entire column could be formatted with the algorithm in one sweep? Not a programmer, so out of my league on this one.

[vmanes]

I'm not sure how your question relates to the OP's problem. But, if you were handling multiple rows, which might have different states in each row, you simply copy this formula row to row, and Excel updates the cell references using its concept of "relative addressing".

[cole davidson]

I was sitting in class today when it hit me! I raced back here to tell you to use an IF function, but it looks like someone else beat me to it! Goodluck!