Home 
Search 
Today's Posts 
#21




Need help making a spreadsheet for my business.
UPDATE: I just thought I'd drop in and let you guys know that I figured out the hardest part of this project today  FINALLY! My brain must have gone into overtime today, lol because It just dawned 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 stock quantities of 12 of their locations seperately, so I needed to figure out a formula to add them all up. They also have a "status" column that states if the product is active or discontinuing, so I needed to make sure the quantity of the products whos status is "discontinuing" was 0. Also, in some of the products, instead of giving an exact stock quantity, they put the word "Available" in the cell that is supposed to contain the quantity. I don't know why they did this, but since I had no idea how many were actualy available, I chose to replace the word "Available" with 99 in the same formula. The status column was column K and the quantity columns were LW. To make this work, first I selected empty cell X2, and made the following formula: Code:  =SUM(L2:W2)*IF(K2="DISCONTINUING",0,1)+IF(L2="Avai lable",99,0)+IF(M2="Available",99,0)+IF(N2="Availa ble",99,0)+IF(O2="Available",99,0)+IF(P2="Availabl e",99,0)+IF(Q2="Available",99,0)+IF(R2="Available" ,99,0)+IF(S2="Available",99,0)+IF(T2="Available",9 9,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 the rest of the cells below it and edit fill  down to fill the rest of the cells in that column with data. The only problem I had with this formula is when I put *IF(K2="DISCONTINUING",0,1) at the very end like I wanted it instead of right after the SUM statement, it didn't calculate right. I dunno why this happened, but it did. So I am just going to have to deal with the very few products whos status is discontinuing, and their quantity is "Available" getting a number other than 0 for now (no biggie). If anyone could tell me why this is happening, I would really love to know. For the next formula, I needed to get my vendor's price from column F and apply that price to my markup chart. For simplicity's sake, I decided not to use the retail markup method. I made up a new chart once 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 forever 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 the rest of the column as earlier. Only took me 6 months to figure out, lol. But I am proud of myself considering that I had already given up and was going to hire someone to do this for me. Now,I just need to get figure out a way to have these formulas automatically applied to my daily vendor spreadsheet, delete the unnecessary rows, and merge with the easy populate spreadsheet from my website so it can be uploaded. I don't think that will be nearly as much of a brain teaser as these damn formulas were for me though.  ElNino  ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725 View this thread: http://www.excelforum.com/showthread...hreadid=391062 
#23




Need help making a spreadsheet for my business.
Don Guillett Wrote: a cursory look suggests a bit shorter countif(l2:w2,"available")*99  Don Guillett SalesAid Software 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:  =(SUM(L2:W2)+COUNTIF(L2:W2,"Available")*99)*IF(K2= "discontinuing",0,1)   ElNino  ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725 View this thread: http://www.excelforum.com/showthread...hreadid=391062 
#24




Need help making a spreadsheet for my business.
glad to help
 Don Guillett SalesAid Software "ElNino" wrote in message ... Don Guillett Wrote: a cursory look suggests a bit shorter countif(l2:w2,"available")*99  Don Guillett SalesAid Software 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:  =(SUM(L2:W2)+COUNTIF(L2:W2,"Available")*99)*IF(K2= "discontinuing",0,1)   ElNino  ElNino's Profile: http://www.excelforum.com/member.php...o&userid=25725 View this thread: http://www.excelforum.com/showthread...hreadid=391062 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
making changes to excel file on Windows 2003 small business server  Excel Discussion (Misc queries)  
Spreadsheet Dilemma  Excel Discussion (Misc queries)  
Scan and copy cells from one spreadsheet to another.  Excel Discussion (Misc queries)  
Some exported records do not show on spreadsheet  Excel Worksheet Functions  
Applying Existing Password to New Spreadsheet  Excel Discussion (Misc queries) 