Need help making a spreadsheet for my business.

[ElNino]

UPDATE:

I just thought I'd drop in and let you guys know that I figured out th
hardest part of this project today - FINALLY!

My brain must have gone into overtime today, lol because It just dawne
on me all of a sudden (strange how these things happen, eh?). Anyways
I'll explain how I was able to do this with 2 simple excel formulas.

First of all, in my vendor's spreadsheet, they give the stoc
quantities of 12 of their locations seperately, so I needed to figur
out a formula to add them all up. They also have a "status" column tha
states if the product is active or discontinuing, so I needed to mak
sure the quantity of the products whos status is "discontinuing" was 0
Also, in some of the products, instead of giving an exact stoc
quantity, they put the word "Available" in the cell that is supposed t
contain the quantity. I don't know why they did this, but since I had n
idea how many were actualy available, I chose to replace the wor
"Available" with 99 in the same formula. The status column was column
and the quantity columns were L-W. To make this work, first I selecte
empty cell X2, and made the following formula:

Code
-------------------
=SUM(L2:W2)*IF(K2="DISCONTINUING",0,1)+IF(L2="Available",99,0)+IF(M2="Available",99,0)+IF(N2="Available",99,0)+IF(O2="Available",99,0)+IF(P2="Available",99,0)+IF(Q2="Available",99,0)+IF(R2="Available",99,0)+IF(S2="Available",99,0)+IF(T2="Available",99,0)+IF(U2="Available",99,0)+IF(V2="Available",99,0)+IF(W2="Available",99,0
-------------------

Then after applying this formula to cell X2, I highlighted it, and th
rest of the cells below it and edit-> fill -> down to fill the rest o
the cells in that column with data.

The only problem I had with this formula is when I pu
*IF(K2="DISCONTINUING",0,1) at the very end like I wanted it instead o
right after the SUM statement, it didn't calculate right. I dunno wh
this happened, but it did. So I am just going to have to deal with th
very few products whos status is discontinuing, and their quantity i
"Available" getting a number other than 0 for now (no biggie). I
anyone could tell me why this is happening, I would really love t
know.

For the next formula, I needed to get my vendor's price from column
and apply that price to my markup chart. For simplicity's sake,
decided not to use the retail markup method. I made up a new chart onc
again,and this is the one I am going to stick with:
<$10 ---------------- Add $3
$10-$49.99 ---------- *1.3 (30%)
$50-$74.99 ---------- *1.2 (20%)
$75-$99.99 ---------- *1.15 (15%)
$100+ --------------- *1.1 (10%)

Once the markup is applied, I needed to output the result to column Y
Here is the magical formula that made this work, which has foreve
eluded me until today:

*drumroll*

Code
-------------------
=IF(F2<10,F2+3,IF(AND(F2>=10,F2<50),F2*1.3,IF(AND(F2>=50,F2<75),F2*1.2,IF(AND(F2>=75,F2<100),F2*1.15,F2*1.1)))
-------------------


Once this code was applied to Y2, I used the same method of filling th
rest of the column as earlier.

Only took me 6 months to figure out, lol. But I am proud of mysel
considering that I had already given up and was going to hire someon
to do this for me. Now,I just need to get figure out a way to hav
these formulas automatically applied to my daily vendor spreadsheet
delete the unnecessary rows, and merge with the easy populat
spreadsheet from my website so it can be uploaded.

I don't think that will be nearly as much of a brain teaser as thes
damn formulas were for me though

 

[Don Guillett]

a cursory look suggests a bit shorter
countif(l2:w2,"available")*99

 

[ElNino]

a cursory look suggests a bit shorter
countif(l2:w2,"available")*99

Hey, thanks Don... it worked! I even figured out why it wouldn't add up
correctly if I placed *IF(K2="discontinuing",0,1) at the end of this
formula (I had to seperate the equations). So, this is my new formula
to add up the quantitty:

Code:

 

[Don Guillett]

glad to help