excel help

[tomas395]

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

[#2]

@tomas395 

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.

[#3]

but how to sum quarters since there is just date

[#4]

@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))

[#5]

Thanks I got it.

Do you have any hints for 2. tasks?

[#6]

@tomas395 

Check out the RANK function.

[#7]

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

[#8]

@tomas395 

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

[#9]

@HansVogelaar 

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

[#10]

@tomas395 

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

[#11]

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

[#12]

@tomas395 

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

[#13]

Could you please clarify it?

[#14]

@tomas395 

I think it's time you did some research yourself.

[#15]

Do not worry that is what I have been doing for the last few hours

[#16]

that is what I have been doing for the last couple of hours