Sorting by days

[sjackson]

Hi there, I am trying to sort an efficiency rate by the days of the week
for a full year. I've attempted to use a sumif statement and divide
that by a countif based on the day of the week, on example is;
=SUMIF($B$150:$IV$150, "Friday",$B152:$IV152) / SUMIF($B$150:$IV$150,
"Friday",$B152:$IV152)*100

The problem that I have is that this formula does not return the right
value. Can anyone help me?

 

[swatsp0p]

I'm not sure what you are trying to solve (why the '*100'?), but you
sample formula does not have a COUNTIF statement in it. Might thi
work for you?:

=SUMIF($B$150:$IV$150,"Friday",$B152:$IV152)/COUNTIF($B$150:$IV$150
"Friday")*100

This will return the average of the amounts entered in cell
$B152:$IV152 that have "Friday" in corresponding cells in row 150

If this is not what you are after, please give more details so we ca
better address your problem

 

[Peo Sjoblom]

Are the values in B150:IV150 dates or text like "Friday"? What do you expect
to get and what did you get, I assume the second sumif is a typo and should
be a countif? If dates you can use (for date fridays)

=AVERAGE(IF(WEEKDAY(B150:IV150,2)=5,B152:IV152))

entered with ctrl + shift & enter

if text

=AVERAGE(IF(B150:IV150="Friday",B152:IV152))


Regards,

Peo Sjoblom

 

[sjackson]

Thanks for the help, I think this might be what I was after. I was
using the *100 because I thought that I wanted to have the decimal
place moved.

 

[swatsp0p]

if text

=AVERAGE(IF(B150:IV150="Friday",B152:IV152))


Regards,

Peo Sjoblom

I was correcting the OP's formula. Peo's solution is more succinct.
Just remember to 'Array' enter his formula (with ctrl + shift
enter).

Glad we could help.

Good luck

 

[Ron Rosenfeld]

Hi there, I am trying to sort an efficiency rate by the days of the week
for a full year. I've attempted to use a sumif statement and divide
that by a countif based on the day of the week, on example is;
=SUMIF($B$150:$IV$150, "Friday",$B152:$IV152) / SUMIF($B$150:$IV$150,
"Friday",$B152:$IV152)*100

The problem that I have is that this formula does not return the right
value. Can anyone help me?

Try a pivot table.

If the contents of row 150 are true Excel Dates, then add a "helper row" with
the formula =TEXT(B150,"dddd") and drag/copy across. Label this row Weekday.

I believe you need to put your data into columns. You could do that by
selecting the (now) three cell range and selecting Copy. Then select a single
cell someplace and Paste Special / Transpose.

Then start the Pivot table wizard (Data/Pivot table) and select to put this on
a new sheet.

Drag "Weekday" to the row area, and "Efficiency" to the Data area.

Right click on "Efficiency" in the table (It'll probably say Sum of Efficiency)
and select Field Settings/Summarize by/ Average.

Format your report.


--ron