excel help

Occasional Contributor

excel help

Hi,

Could anyone help me with following tasks using just one excel formula? (dataset below)

1.Percentage of quarter's sales made by each trader.  (for instance David´s sales for Q1..)

David         James           Sarah

Q1

Q2

Q3

Q4

2. 10. Who had the first, second and third highest sales by names?

3. . What percentage is each sale for all those made in the same year?

Thank you for any help

Tom

15 Replies

Re: excel help

Since this is homework, I'll just provide a hint: you can use the SUMIFS function to sum the sales of a specific trader in a specific quarter, and also to sum all sales in the same quarter. The quotient of the two sums will return the percentage.

Re: excel help

but how to sum quarters since there is just date

Re: excel help

@tomas395 You need to specify the date range twice in the SUMIFS formula. For example, Q1 of 2013 is from January 1, 2013 to March 31, 2013.

=SUMIFS(date_range, ">="&DATE(2013,1,1), date_range, "<="&DATE(2013,3,31))

Re: excel help

Thanks I got it.

Do you have any hints for 2. tasks?

Re: excel help

Check out the RANK function.

Re: excel help

Yes but there is problem , how to sum sales for each person

Re: excel help

You can use SUMIF or SUMIFS to sum the sales per person.

Re: excel help

and answer for this task should be name of person. So who has highest, second highest and third highers sales. Any ideas?

Re: excel help

You can create a list of persons and sales and sort it descending on sales.

Re: excel help

@Hans Vogelaar   I do not think it will work out, I need just one formula(see below)

Re: excel help

You'll need to use a combination of INDEX and MATCH.