EXCEL Formula Help

Question

zteam777 0 Newbie Poster

15 Years Ago

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

windows-vista-7-8

5 Contributors
7 Replies
109 Views
5 Days Discussion Span
Latest Post 14 Years Ago Latest Post by cole davidson

Bob_180_Bob 41 Posting Virtuoso

15 Years Ago

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

vmanes 1,165 Posting Virtuoso

14 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kaninelupus 275 Practically a Posting Shark · Answer 1 · 2009-11-16T16:21:27+00:00

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 0 Newbie Poster · Answer 2 · 2009-11-19T02:48:35+00:00

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.

kaninelupus 275 Practically a Posting Shark · Answer 3 · 2009-11-19T14:36:40+00:00

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 1,165 Posting Virtuoso · Answer 4 · 2009-11-19T21:28:48+00:00

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 0 Newbie Poster · Answer 5 · 2009-11-19T23:14:02+00:00

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!