×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MS Excel - Avg and Number of Sales and Re-Sales over intervals

MS Excel - Avg and Number of Sales and Re-Sales over intervals

MS Excel - Avg and Number of Sales and Re-Sales over intervals

(OP)
Have a voluminous monthly MS Excel worksheet (Over 240,000 records) with equipment sale and re-sale data throughout the
year such as displayed below.


EqNo------- Region------ SaleAmount-------SaleDate
A-250------- 146------- 8,000------- 1/5/2018
A-300------- 146------- 25,000------- 1/7/2018
A-400------- 146------- 6,800------- 1/15/2018
A-589------- 146------- 5,800------- 2/3/2018
A-600------- 146------- 6,004------- 2/15/2018
A-250------- 146------- 10,000------- 3/4/2018
A-300------- 146------- 27,000------- 4/15/2018
A-400------- 146------- 7,500------- 9/15/2018
A-589------- 146------- 5,000------- 8/25/2018
A-600------- 146------- 20,000------- 11/10/2018
B-200------- 147------- 84,503------- 2/1/2018
B-300------- 147------- 25,450------- 1/23/2018
B-400------- 147------- 7,250------- 1/5/2018
B-550------- 147------- 6,250------- 3/15/2018
B-655------- 147------- 16,454------- 1/2/2018
B-250------- 147------- 20,450------- 3/17/2018
B-200------- 147------- 100,000------- 5/15/2018
B-300------- 147------- 28,000------- 6/2/2018
B-400------- 147------- 8,250------- 10/23/2018
B-550------- 147------- 5,000------- 6/8/2018
B-655------- 147------- 30,000------- 6/26/2018
B-250------- 147------- 35,000------- 8/25/2018


Objective is to populate the MS Excel worksheet as displayed in the second image below.

In other words, I need to determine the variance between the Equipment sale prices over time and create "buckets" to display the number of equipment, the average sales price and average re-sale prices for the equipment by month by "buckets."

Note, each piece of equipment can be sold and re-sold multiple times during the year.

The time buckets are as follows;

# 0 - 6 Month Resale
0-6 Months Avg purchase price
0-6 Month avg resale price
# 6-12 Months Resales
6-12 Months Avg purchase price
6-12 Month avg resale price
# 0 -12 Month Resales
0-12 Months Avg purchase price
0-12 Month avg resale price

So far, I have contemplated adding a "helper" column to calculate the difference between the equipment sale dates for each unique equipment and then use a pivot table to group the data by month.

Then considered the use of sumproduct.

Still reviewing/experimenting with various options... objective.

I have attached an MS Excel spreadsheet for reference.

Appreciate any insight as to an efficient method to accomplish the objective.







RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

Hi,

You have attached nothing!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

Please explain what the month spans mean? For instance when does 0 - 6 months start and end?

How do you differentiate the Purchase Price from the Resale Price?

Need these before attempting any solution.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

(OP)
Well, this particular problem is one of the most challenging...

It appears that "0 - 6 months" refers to the case when a particular equipment is sold and then re-sold within 6 months.

So, if equipment A is sold initially on January 1, 2018 and then sold again on March 1, 2018, then it should be counted as "1" in the January 2018 bucket (because it was initially sold during January 2018) under the column "0 - 6 months" because it was sold again within 6 months.

Then, it appears that I would need to average the sale price for all of the equipment that was initially sold during January 2018 and also average the re-sale prices for each equipment that was initially sold during January 2018 while noting the intervals between the initial sold date during January 2018 and the subsequent re-sale date throughout the year.

Perform the same for the equipment that was initially sold during February 2018.

And so on.


RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

As previously noted...

1) you have attached nothing!

2) How do you differentiate the Purchase Price from the Resale Price?

Another question: Average based on what values as a base? A specific equipment?

Please give formula or logic for a specific example.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

(OP)
As far as the distinction between Purchase Price and Resale Price, I initially thought that there was missing data.

It appears that anytime that the equipment is sold again, then the price is considered a re-sale.

It very well could be that there is equipment that has not been re-sold during the year.


RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

I purchase a Widget for $1.00 in January.

Then I sell the Widget for $1.25 in February.

How do I sell the widget again, for instance in July unless I re-purchase before re-selling?

I am TOTALLY confused!

Notice that the Purchase price has nothing to do with any sale or resale price.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

(OP)
It appears that the Widget is "repurchased" by the second owner.

For this problem, it appears that "repurchase" does not imply the original owner but only used to designate that the particular equipment is sold again - albeit to a different owner each time.



RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

Still don’t know a single thing about the purchase price.

All you have are SALE prices!

What? We have data from different “owners”?

This is data for a SELLER. the SELLER must first have purchased an A-250 before he can SELL an A-250.

Where is the purchase price for the A-250?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

(OP)
Apologize for the confusion.

Inadvertently selected data from the incorrect worksheet.

Displayed below is what appears to be the appropriate data.

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

(OP)
Thank you all for the pointers.

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

No COPY ‘n’ PASTE data?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

“Displayed below is what appears to be the appropriate data.”

Appears? Don’t you know for sure?

Why do we have Purchaser? Are these purchasing agents for the seller? Is this relevant data?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

Just looked at your PICTURE closer, and noticed that Sam, Shila, Ted, Kate, Phil are on the SELL side and the PURCHASE side with same dates and amounts???

That makes absolutely no sense!

The SELLER is what this seems to be about. The SELLER must make Purchases in order to have stock. Some Purchases that the SELLER makes are original Purchases and some seem to be re-Purchases at some later date after the SELLER Sells an item.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

The way I ‘read’ this picture is:
Jane had purchased equipment A-250 (from somebody) for $4,000 on 1/1/2018 and she sold it to Sam for $8,000 a few days later on 1/5/2018 (nice mark-up of 100%, BTW). Then Sam had sold this A-250 (to somebody) for $10,000 on 3/4/2018

Or a story of A-600
Mary bought it (from someone) for $1,000 on 1/10/2018 and sold it to Phil for $6,004 on 2/15/2018 (nice mark-up!), and then Phil sold it to (somebody) for $20,000 on 11/10/2018
What is this A-600 because I want to invest in it – 20 times more in 11 months! Bernard Madoff – eat your heart out!

Looks like money laundering enterprise to me… ponder


---- Andy

There is a great need for a sarcasm font.

RE: MS Excel - Avg and Number of Sales and Re-Sales over intervals

So how do we account for an item that is purchased and added to the inventory and has no Sale at report time; therefore SaleAmount and SaleDate are empty.

How would this be handled?

Suppose this item is sold one time, so this item does have a single SaleAmount and SaleDate.

How would this be handled since there is no resale to compare?

How are month differences to be calculated? Month has no exact mathematical definition. These differences could be calculated in a number of different ways as integral or decimal values.

It also seems to me that purchases and sales need to reside in separate tables. My experience in manufacturing resource systems has tables for requirements (sales or demand) and other tables for replenishments (purchases or supply). They are often joined to display net results (inventory, requirements and replenishments net results showing surpluses and shortages). In order to calculate the month differences, separate tables will be necessary, I believe.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close