Trying to get a formula and calculation to work, need help.

[pigment01]

I am trying to get this exercise completed, I have tried on my own but I am stumped. Help will be much appreciated. Thanks!

Part 1

Open the provided Excel file “ExerciseExcel.xls”. The first worksheet (Code Conversion) has a column labeled “Length of Employment” (Column A). Convert each length of employment into one of the following categories:

0-6 (0 to 6 Months)
7-12 (7 to 12 Months)
1-2 (1 to 2 Years)
2-5 (2 to 5 Years)
5+ (5 plus Years)

Column C labeled “After Conversion” contains examples of the conversion for the first 38 rows. Use whatever method you want to complete the conversion (except for manually converting the numbers) in Column B.


Part 2

Please refer to the worksheet in the "ExerciseExcel" file called “Data”. Please calculate the median “Avg Guest Check – Overall” for each segment (A,B,C, and Q) for only those average guest checks that are greater than zero. Please save the file with your calculations and/or provide a description of how you accomplished this.

[63falcondude]

I don't think that it is a good idea to mix units (e.g. 7-12 months and 1-2 years)

That being said, if that is what you want to do then you can try this:

=LOOKUP(A2,{0,0.58,1.01,2.01,5.01},{"0-6","7-12","1-2","2-5","5+"})

[pigment01]

Thanks for your help. Could you also help me how to do Part 2? I was thinking a pivot table but I am not sure.

Part 2

Please refer to the worksheet in the "ExerciseExcel" file called “Data”. Please calculate the median “Avg Guest Check – Overall” for each segment (A,B,C, and Q) for only those average guest checks that are greater than zero. Please save the file with your calculations and/or provide a description of how you accomplished this.

*This is in the same file attached

[63falcondude]

Please refer to the worksheet in the "ExerciseExcel" file called “Data”. Please calculate the median “Avg Guest Check – Overall” for each segment (A,B,C, and Q) for only those average guest checks that are greater than zero. Please save the file with your calculations and/or provide a description of how you accomplished this.

Try

=MEDIAN(IF(($E$2:$E$76="A")*($D$2:$D$76>0),$D$2:$D$76)) Ctrl Shift Enter

"A" can be changed to a cell reference such as G2 (with G2 having "A" in it without quotes).

[AliGW]

What have you tried so far for part two?

[pigment01]

Hi there,

This is what I have completed so far, I think I did it correctly? But I am sure there is a more efficient way to do this.

Please advise.

Thanks,

Pigment01