Is there a way to change 1 number in "Average" Formula?

dannyg · December 25, 2023, 12:56pm

This is just a sample sheet I uploaded so you can see what I’m talking about.
I don’t want to create any more Columns on my actual spreadsheet.
I know I can create another 2 Columns in Rows 1-12 and name them “Minus Bonus Pay” and “Net Pay” and then use the Column “Net Pay” for my daily averages.
Trying to avoid doing that since I have other calculations on the sheet that are in nice order and all fit on a screen without scrolling. I have no more room to “Shrink it down”.

I don’t have many days that have “Bonus Pay” maybe 2 or 3 a month.

In Row 16 I have all the totals for “Sundays”
Row 17 “Mondays” and so on.

In Cell E16 I have the Formula that works perfect for the “Total Pay” average of “Average Per Day”.
=AVERAGEIF(B2~B5~B8~B11,"<>0")

IS there a way I can get the “Net Pay” to Correctly show my “Average Per Day” so that the “Average Per Day” is actually the amount from Cell D16 ?

Even If I have to change the formula to “Subtract” the "Bonus amount from the formula that would be ok.

I need the “Average Per Day” to be the Average it refers to, not the “Total Average”.

I tried adding
-30 at the end of “=AVERAGEIF(B2~B5~B8~B11,”<>0")"
But that don’t work.

I’m trying to do this without having to add 2 more columns to the Sheet from A1-12
Untitled 1.ods (20.5 KB)

Villeroy · December 25, 2023, 1:03pm

Delete the rows where nothing has been payed.

Steph1 · December 25, 2023, 1:15pm

I have a solution using my favorite function SUMPRODUCT!
AveragePay.ods (21.2 KB)

JohnSUN · December 25, 2023, 1:20pm

Please look in Tools - Options - LibreOffice Calc - Calculate for the Formulas Wildcards radio button group.

If you have the first item Enable wildcards in formulas active (as in the screenshot), then your formula should be

=AVERAGEIFS($B$2:$B$11;$A$2:$A$11;$A16&"*";$B$2:$B$11;"<>0")

If you have the Enable regular expressions in formulas item active, then your formula should be

=AVERAGEIFS($B$2:$B$11;$A$2:$A$11;$A16&".*";$B$2:$B$11;"<>0")

And finally, if the third item No wildcards or regular expressions in formulas is active then disable the item Search criteria = and <> must apply to whole cells and use the formula

=AVERAGEIFS($B$2:$B$11;$A$2:$A$11;$A16;$B$2:$B$11;"<>0")

dannyg · December 25, 2023, 1:22pm

THANK YOU !

That will work perfect !

I’m not good with Formulas at all.
Everyone here is the best !

Thank you again …

dannyg · December 25, 2023, 1:24pm

I have to get more familiar with that “Calculate Options”.
Setting “Defaults” would make it a lot easier for me.

JohnSUN · December 25, 2023, 1:27pm

It seems to me that “By default” the second item was active - now it’s hard to remember: when preparing answers to various questions here, I switched this many times in different combinations.

Wanderer · December 25, 2023, 4:48pm

It seems to me, you are a long term user - the default was changed, when wildcards were added
https://bugs.documentfoundation.org/show_bug.cgi?id=88581

This is only a “per file” setting, and even my setting (regular expressions) should not be applied all time: When importing xlsx wildcards can be expected, regular expressions not.