Forum Discussion
Cells not calculating based on IF/OR formula entered
I'm having trouble with a spreadsheet I created calculating sales tax based on where work was done but the trouble is only applying to certain columns I entered figures in.
Before I get any further, I'm using Excel for Mac vers. 16.78
So, I have 3 locations I enter sales for and based on the location, sales tax is supposed to be calculated on the first $500. The percentage of sales tax varies based on location. I also need to show the amount over $500 and sales tax for outside the cities and that amount over $500. I have formulas that I created that calculates the correct amount for one city column, but for some reason, will not calculate for the other two cities and the outside cities columns.
For example, row A4, in cell C4, it correctly calculated in cells K4, L4, M4 and N4 using the following formulas:
K4: =IF(C4>500,500*4.85%,C4*4.85%)
L4: =IF(C4>500,C4-500,0)
M4: =IF(C4>500,500*3%,C4*3%)*OR(D4>500,500*3%,D4*3%)*OR(E4>500,500*3%,E4*3%)
N4: =IF(C4>500,C4-500,0)*OR(D4>500,D4-500,0)*OR(E4>500,E4-500,0)
If you scroll down to row A26, I entered the sales in E26, but the formulas for I26 and J26 worked but M26 and N26 (the same formulas as M4 and N4 except for cells 26) did not. If I change cell M26 to =IF(OR(C26>500,D26>500,E26>500),500*3%,OR(C26*3%,D26*3%,E26*3%)), it will calculate correctly and pull the information from whichever cell has the figure, but I can't get N26 to calculate a figure (I just realized the formula I tried to change for M26 doesn't work for anything less than $500 so that formula is out). I tried changing the formula to =IF(OR(C26>500,D26>500,E26>500),OR(C26-500,D26-500,E26-500),0) but it will only return TRUE instead of any figures.
I have checked and there are no conditional formatting rules applied and I don't get any error messages.
I should also add that I am not very Excel experienced. What I know I have learned on my own or from online searches so I may require very clear and simple explanations to know what you are talking about.
Thank you so much for any help anyone can offer!
1 Reply
It's not altogether clear what you're trying to do, but I'll make a couple quick suggestions, offer a sample of one aspect of how you should be thinking about this (the kind of thing you'll learn as you delve more into Excel).
First, it's generally acknowledged that the more you can avoid "hard-coding values" into a formula, the better. Specifically in this case, don't include the 4.0% or 4.85% tax rates IN the formula itself. Instead, use a table, and look up the value. The use of a table enables you to change the tax rates next year (or whenever they change) WITHOUT changing the formula.
And a table also allows you, as your locations expand to more cities, more states, to just add those new tax rates to the same table, again without any changes needed to the formula.
Then there's the VLOOKUP function, which in this case takes the location code and checks the table for the relevant column and retrieves the appropriate tax.
So you'll see all of that in the attached spreadsheet.
IF you want more help with your specific spreadsheet, please post a copy of the spreadsheet itself (without proprietary or confidential data) on OneDrive or GoogleDrive and paste a link here that grants access.
