Help solving profits in excel

**pepsi** · 03-16-2009, 02:29 PM

This is solved (i dnt know how to edit the title ) thanks everyone

Hello, first off i'd like to point out that i can't upload the excel spreadsheet as its an assignment for Alevels, and I'm just after a solution to how i could solve the following

I have these cells in one spreadsheet called 'sales_data'

Product code | QTY | Unit Cost | Unit Price | Gross Profit
-----1--------------43----------4.00------------6.00--------(6.00-4.00)*43= 86

then in another called Bonuses i have the following info

Sales above | Bonus
$30000 | 500
$40000 | 1000
$50000 | 2000

so if sales reach 30000 then the person will get $500 bonus, the sales person also earns $3000 a month

The part i'm getting mostly stuck with is on my spreadsheet i have dates like 4/25/2007 that format, but if the sales person get $3000 a month, how do i group that dates up to make a month (would something like subtotals work for that?)
As i need to also calculate when the gross profit exceeds the 'sales above' for a quarter (which would be easy to calculate once i can group the months together right? )

I'm just looking for some assistance in my thinking towards solving this, what would you do?

**dustycrockett** · 03-16-2009, 05:10 PM

The question is really too confusing. How is the "$3,000 per month" connected to the bonus? How is it connected to the sales data?

How do you know how much a salesperson sells? Does sales data track the name of the sales person?

It appears you're not tracking sales, only gross profit.

Are you sure you know enough about the subject matter to model it in a spreadsheet?

**pepsi** · 03-16-2009, 05:39 PM

Hye sorry if its confusing

The question is really too confusing. How is the "$3,000 per month" connected to the bonus? How is it connected to the sales data?

the $3000 per month is separate they get this fixed amount per month, then they will get bonuses on top

How do you know how much a salesperson sells? Does sales data track the name of the sales person?

I know because in the 'sales_data' spreadsheet under QTY it says 43 which is how much they sold.

It appears you're not tracking sales, only gross profit.

I am tracking sales, the data below isn't my full spreadsheet, just the necessary data needed to figure out my problem, the QTY is sales data (should of made that clearer before)

Are you sure you know enough about the subject matter to model it in a spreadsheet?

I think i know enough to complete this problem (as far as excels technical side goes) as i believe i'm only going to be using vlookup and a few basic multiplication and subtracting formulas

Its just the problem solving side of it, im not 100% sure how im going to complete that side

**JBeaucaire** · 03-16-2009, 05:40 PM

Some functions for you to read up on to probably solve your problem:

MONTH()

SUMPRODUCT(--(MONTH(RangeOfDates)=1),MatchingRangeToSum)

LOOKUP(SUMPRODUCT(--(MONTH(RangeOfDates)=1),MatchingRangeToSum),RangeOfDollars,RangeOfBonus,FALSE)

SUMIF(Range,Criteria,[optSumRange])

**pepsi** · 03-16-2009, 06:28 PM

hey thanks for the reply, ill look up on some of those functions now, but i'm not sure if i fully understand the function codes you've mentioned, could you maybe elaborate a little using pseudocode please

**JBeaucaire** · 03-16-2009, 07:02 PM

MONTH()
If cell A1 holds the value 5/19/2009 (M/D/Y) then the formula =MONTH(A1) returns the value of 5, the month from that date.

SUMPRODUCT()
Used to sum a range after using other range/criteria to filter down.

Please Login or Register  to view this content.

LOOKUP(Value,LookupRange,ReturnRange)

You original question is of the LOOKUP variety. You want to find a total amount money in a particular date range (Value), then lookup that value in your original chart to find the corresponding Bonus:

Please Login or Register  to view this content.

One of the benefits of LOOKUP is that it rounds down, so 44000 matches the 40000 range.

So, now you can use that entire process to build up on huge formula encompassing all those pieces, like I illustrated in the previous post.

**dustycrockett** · 03-16-2009, 07:12 PM

Originally Posted by pepsi

...
I know because in the 'sales_data' spreadsheet under QTY it says 43 which is how much they sold.
...

Ahh....it was the table in your original post that threw me off...I'm accustomed to thinking of "$30000" as "Thirty thousand dollars" instead of 30000 units.

Originally Posted by pepsi

I think i know enough to complete this problem (as far as excels technical side goes) as i believe i'm only going to be using vlookup and a few basic multiplication and subtracting formulas

Didn't realize how that question sounded till I read it again -- I usually manage to avoid being a jerk. sorry bout that.

Never saw SUMPRODUCT used like that before...I'll have to remember that.

**JBeaucaire** · 03-16-2009, 07:33 PM

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

**pepsi** · 03-17-2009, 08:31 AM

thanks i'll give it ago over the next hour see how i get on

**pepsi** · 03-17-2009, 09:20 AM

hmm its going ok, but i have another quick question

how do i count how many changes in numbers are in a list

basically i have

2
2
2
2
2
-change here
3
3
3
3
3
-change here
4
4
4
4

Then here in a subtotal i want to calculate the changes in the numbers for instance this would be 2 is there away, or is there an alternative way such as calculate cell A1 - Cell 14?

thanks again

**JBeaucaire** · 03-17-2009, 02:13 PM

No idea what any of that means. Sorry. Perhaps posting an actual workbook showing before data / after data and your expected results from the formula you're seeking. Highlight the pertinent cells if necessary to make it clear.

Help solving profits in excel

LinkBack

Thread Tools

Rate This Thread

Display

Help solving profits in excel

Re: Help solving profits in excel

Re: Help solving profits in excel

Re: Help solving profits in excel

Re: Help solving profits in excel

Re: Help solving profits in excel

Re: Help solving profits in excel

Re: Help solving profits in excel

Re: Help solving profits in excel

Re: Help solving profits in excel

Re: Help solving profits in excel

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions