Formula for calculating average of Specific Cells using "Find"

English
#1

I realize a problem with my sheets finding an “Average” for Same Days in the week of 31 rows for a month.
In the Sheet I posted I need the Average: for example … The total of all “Sundays” that are in P3 - P33, Minus G37 to get the Average of all “Sundays” in L37.
Same for Monday, Tuesday, etc.

In my formula it’s wrong. In L37 that is giving me the “Average of the Averages” from B37-F37.

I need the “Average” of all the “Sundays” from P3 - P33 then Minus G37 so I know what my true average is minus the “Bonus” amount from that total in L37.

Untitled 1.ods (54.1 KB)

#2

Another issue might be simple

In this sheet R38 is correct and in the Formula it “Looks for” “Monday” in A38 it references to.

If I change the name in that cell to “Sunday 2” for example, all the cells go blank because it’s only looking for “Sunday”, not a “Sunday 2”.
How do I change the formula to make it only looks for “Sunday” in the cell and ignore the “2”.
2024 Test Sheet.ods (47.1 KB)

#3

maybe give an idea of what value you expect … :thinking:
cause it’s quite jungle here …

image
image1351×633 199 KB

so that’s the one related to the tilte of your topic ?
cause the first post didn’t menton FIND at all :face_with_thermometer:

not sure, but I would definitely start with helper columns to factorize this … jungle :innocent:
image

Documentation/Calc Functions/EXACT - The Document Foundation Wiki

#4

Typed the last part wrong In Untitled 1 ods

This is what I need …
I need the “Total Amount” of all the “Sundays” from P3 - P33
Then subtract G37 from that Amount
And then that Amount to show the Average in L37

#5

0 cause there re all OFF ? :thinking:

#6

Ok then use something like “Thursday”.
I need them for each day of the week, so if Thursday can be figured out I an change it for the rest of the days

#7

You would have to use real date entries instead of these fanciful fake-dates, then a large part of your problems would already be solvable without fragile formulas!

#8

as previously stated: try to clarify what you need.

hint : seems to be just Documentation/Calc Functions/SUMIF - The Document Foundation Wiki

plenty of examples everywhere …
SUMIF and days of the week - #2 by Steph1

#9

There is a workaround. But I need the formula.
I can select each day for example like lets use "Tuesday

P9 = 43.30
P16=47.51
P23 = 0
P30= 0

Subtract G39 from that total

Then divide that number by K39 without a Div0! error

So basically a working formula for

P9 + P16 +P23 +P30 -G39 /K39 to give me THAT result in L39

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.