Averaging data corresponding to specific weekdays

Nico_2016 · 17. Juni 2018

Hello,

I would like to calculate the average value for a specific weekday. (In this case the average value for Sunday)

The values in column A range from 2016 to 2020. The values is columns B through E are populated day by day, so obviously the cells after today's date remain empty.

Simply put, I'd like to calculate the "total average" of all "average values for Sundays". So basically sum all average values (column E) corresponding to a Sunday and then dividing that number by the total number of Sundays which have populated values in column E.

Criteria 1: Data in column A must be "Sunday"

Criteria 2: Data in column E must NOT be empty.

I'd relly appretiate your help with the formula and a brief explanition for dummies. I do like to understand my formulas ;-)

Thanks,

Nico

Nico_2016

Hi Claus,

Danke, ich glaube es hat geklappt.

Ich bin nicht so gewandt mit Excel-Formeln. Könntest du mir ein paar spezifische Teile erklären?

- Was macht die Formel "Summenprodukt" und warum brauche ich diese?

- Warum braucht man --?

- Warum braucht man "N"?

Grüsse und Dank

Nico

Claus Busch

Hallo Nico,

du bist hier im deutschen Forum.

Probiere es mit:

=SUMMENPRODUKT(--(WOCHENTAG(A2:A1000)=1);E2:E1000)/SUMMENPRODUKT(N(WOCHENTAG(A2:A1000)=1))

bzw.

=SUMPRODUCT(--(WEEKDAY(A2:A1000)=1),E2:E1000)/SUMPRODUCT(N(WEEKDAY(A2:A1000)=1))

Ich denke, die Formel ist selbsterklärend.

Claus

Claus Busch

Hallo Nico,

schau mal hier:

https://support.office.com/de-de/article/summenprodukt-funktion-16753e75-9f68-4874-94ac-4d2145a2fd2e

Du benötigst hier SUMMENPRODUKT, weil man in SUMMEWENN und ZÄHLENWENN keine weitere Formel (WOCHENTAG) unterbringen kann.

Der Teil:

WOCHENTAG(A2:A1000)=1

ist entweder WAHR oder FALSCH. Um diese Wahrheitswerte in Zahlenwerte zu wandeln damit SUMMENPRODUKT damit rechnen kann, steht das doppelte Minus davor in dieser Syntax. Man kann das auch umgehen, indem man beide Argumente miteinander
multipliziert:

=SUMMENPRODUKT((WOCHENTAG(A2:A1000)=1)*E2:E1000)/SUMMENPRODUKT(N(WOCHENTAG(A2:A1000)=1))

Der zweite SUMMENPRODUKT-Teil zählt den entsprechenden Wochentag. Da hier kein zweites Argument zur Multiplikation zur Verfügung steht, wird hier mit N multipliziert. Auch dies kann man durch 1* Argument ersetzen:

=SUMMENPRODUKT((WOCHENTAG(A2:A1000)=1)*E2:E1000)/SUMMENPRODUKT(1*(WOCHENTAG(A2:A1000)=1))

Ich arbeite aber lieber mit der Originalsyntax, bei der die Argument mit Semikolon getrennt werden.

Claus

Scottgem

Hi Abraham, I'm an independent adviser and will try to help.

When I make a new database, the first thing I do is make a list of all the data points that I need to capture. I then organize those data points into logical, normalized tables. The Foundation of any database is a well done normalized table structure.

What you might try to start with is tool or video rental structure to give you the base you want. You can search on 'database models' to find sights that have models to learn from.

This medium is more suited to asking specific questions, rather than a generalized tutorial. I hope I've provided some starting points for you. If you need further clarification please feel free to ask.

bVisual

It is OK to use the Org Chart Wizard to create the structure of an org chart, then to use External Data to update the data. However, they have conflicting methods of displaying data. In this case, I recommend turning off the Data Graphics by unticking the Data \ Advanced Data Linking \ Advanced Data Graphics dropdown menu before linking the data.
 Alternatively, if the shapes have already been linked, you may be able to:
 1. Ensure nothing is selected
 2. Tick the Data \ Show/Hide \ Data Graphics option to open the panel, then untick any fields for the recordset
 Or
 1. Select all of the org chart shapes
 2. Apply the No Data Graphic option from the Data Graphics by unticking the Data \ Advanced Data Linking \ Advanced Data Graphics dropdown

Hi,

willkommen im Windows Forum!

Averaging data corresponding to specific weekdays - Similar Threads - Averaging data corresponding	Forum	Datum
Data c Data c: guten abend. mein disk c ist full, aber warum ich hab nichts am Data C und habe ich ich eine spiele heisst es Minecraft und es habe ich in data d herunterladen aber spiel schreibt was minecraft...	Microsoft Office	15. März 2024
Is it possible to assign a certain view to a specific folder in a Document Library? Is it possible to assign a certain view to a specific folder in a Document Library?: In Sharepoint: I have two folders in a Document Library and I have a term store which is only necessary for one of these folders. The other folder does not need the term store. I have created two...	Microsoft Office	5. April 2023
Visio Data Vizualisation Visio Data Vizualisation: Unterstützt Visio 2019 Professional die Excel Office 365 Vorlagen zur Data Visualization wie zum Beispiel zur Erstellung eins Cross Functional Flowchart? Wenn nicht welche Visio Version wird dazu...	Microsoft Office	5. Februar 2020
How to link ONE of my two sending e-mail-adresses to specific recipients How to link ONE of my two sending e-mail-adresses to specific recipients: I use in Office 365 ProPlus Outlook Version 1910. I own two E-mail sending addresses: address a and address b. Address a for recipients type A and b for recipients type B. By error I use...	Microsoft Office	11. November 2019
Save Data in apples official data-app Save Data in apples official data-app: Greetings, i have a question. When I try to save a page, the only place I can do so is OneDrive. But I would like to save it in apples official Date app, to synchronize it with my other data. Is...	Microsoft Office	5. Mai 2019
Convert "million" and "billion" to corresponding numbers Convert "million" and "billion" to corresponding numbers: Hello everybody, I have a set of data with entries such as "1.33 billion" and "233 million". In order to do calculations with the data I would like to convert that to e.g. "1,330,000,000" and...	Microsoft Office	13. Oktober 2018
wegen Data Residency wegen Data Residency: Habe 2 outlook com konten und beide auf demselben Computer und mit derselben IP Adresse angelegt. Doch jetzt sagt die Outlook app eins sei aus USA und das andere aus Europa. Wie kann ich das...	Outlook.com	29. Mai 2017

Averaging data corresponding to specific weekdays