1 column to 7

Ralter649

New Member
Joined
Oct 20, 2017
Messages
36
I have a list of dates in 1 column and a number in the next column. I need to make a column of days of the week with the number in the correct column. OR just a way to find the avg. of all of the numbers for everyday of the week.

1/1/20 57
1/2/20 49
1/3/20 6
1/4/20 178

to

mon tue wed.....
57 49 6
22 69 124

or

mon avg: 34.8
tue avg: 68.8
wed avg: 138.4


Thanks for the help.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
what version of excel are you using, solutions will change depending on version- also useful to update profile

i'm also not following your example

1/1/20 - is a Wed not a monday

=AVERAGE(FILTER(B1:B4,WEEKDAY(A1:A4)=WEEKDAY(F1)))

Book6
ABCDEF
11/1/2057Wed4/1/20
21/2/2049Thu57
31/3/206Fri
41/4/20178Sat
Sheet1
Cell Formulas
RangeFormula
F2F2=AVERAGE(FILTER(B1:B4,WEEKDAY(A1:A4)=WEEKDAY(F1)))
D1:D4D1=A1


Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

----
 
Last edited:
Upvote 0
what version of excel are you using, solutions will change depending on version- also useful to update profile

i'm also not following your example

1/1/20 - is a Wed not a monday

=AVERAGE(FILTER(B1:B4,WEEKDAY(A1:A4)=WEEKDAY(F1)))

Book6
ABCDEF
11/1/2057Wed4/1/20
21/2/2049Thu57
31/3/206Fri
41/4/20178Sat
Sheet1
Cell Formulas
RangeFormula
F2F2=AVERAGE(FILTER(B1:B4,WEEKDAY(A1:A4)=WEEKDAY(F1)))
D1:D4D1=A1


Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

----

I am using microsoft office 365
how is this.....

go from:
Screenshot 1.png


to something like this:
Screenshot 2.png
 
Upvote 0
How about?

Book1
ABCDEFGHIJ
115-May9sunmontuewedthufrisat
214-May779975110
313-May103976148
412-May126 81027
511-May5       
610-May7       
709-May9       
808-May9       
907-May3       
1006-May8       
1105-May4       
1204-May1       
1303-May6       
1402-May7       
1501-May6       
1630-Apr2       
1729-Apr7       
1828-Apr2
1927-Apr10
2026-Apr8
Sheet3
Cell Formulas
RangeFormula
D2:J17D2=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$1:$A$20)/(TEXT($A$1:$A$20,"ddd")=D$1),ROWS($1:1))),"")
 
Upvote 0
Another option with Excel 365
Note you don't need column C. This was just for a check of the formula to make sure days matched.
Book1
ABCDEFGHIJ
15/15/202358MonSunMonTueWedThuFriSat
25/14/202365Sun65583425964758
35/12/202347Fri8543672415
45/11/202396Thu2868342718
55/10/202325Wed995885
65/9/202334Tue
75/8/202385Mon
85/5/202315Fri
95/4/202324Thu
105/3/202367Wed
115/2/202343Tue
125/1/202328Mon
134/28/202318Fri
144/27/202327Thu
154/26/202334Wed
164/25/202368Tue
174/24/202399Mon
184/21/202385Fri
194/20/202358Thu
204/15/202358Sat
Sheet3
Cell Formulas
RangeFormula
D2,J2,H2:I5,F2:G4,E2:E5D2=FILTER($B$1:$B$20,TEXT($A$1:$A$20,"ddd")=D$1,"")
C1:C20C1=TEXT(A1,"ddd")
 
Upvote 0
You're welcome. Glad we could help, thanks for the feedback.
 
Upvote 0
I am using microsoft office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top