Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #21   Report Post  
Old January 31st 06, 11:45 PM posted to microsoft.public.excel.newusers
ElNino
 
Posts: n/a
Default 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 L-W. 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


  #22   Report Post  
Old February 1st 06, 02:51 PM posted to microsoft.public.excel.newusers
Don Guillett
 
Posts: n/a
Default Need help making a spreadsheet for my business.

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

--
Don Guillett
SalesAid Software

"ElNino" wrote in
message ...

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 L-W. 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
making changes to excel file on Windows 2003 small business server Michael Flowers Excel Discussion (Misc queries) 6 July 5th 05 02:55 PM
Spreadsheet Dilemma msbates2004 Excel Discussion (Misc queries) 1 May 27th 05 02:55 AM
Scan and copy cells from one spreadsheet to another. Mark Excel Discussion (Misc queries) 1 April 6th 05 06:45 PM
Some exported records do not show on spreadsheet vulcan88 Excel Worksheet Functions 0 March 30th 05 01:11 AM
Applying Existing Password to New Spreadsheet Vic Excel Discussion (Misc queries) 1 January 27th 05 01:37 AM


All times are GMT +1. The time now is 07:21 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017