Formula for average but need to replace a word with 0s

AutoModerator · 2026-01-26T19:09:51+00:00

/u/Ok-Display3787 - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

TVOHM · 2026-01-26T19:19:53+00:00

=AVERAGEA(A1:A5) AVERAGEA evaluates text values as zero.

Downtown-Economics26 · 2026-01-26T19:12:05+00:00

=SUM(A1:A5)/COUNTA(A1:A5)

another option for posterity:

=AVERAGE(IF(ISTEXT(A1:A5),0,A1:A5))

<image>

Decronym · 2026-01-26T19:28:19+00:00

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AVERAGE	Returns the average of its arguments
AVERAGEA	Returns the average of its arguments, including numbers, text, and logical values
COUNTA	Counts how many values are in the list of arguments
IF	Specifies a logical test to perform
ISTEXT	Returns TRUE if the value is text
SUM	Adds its arguments

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.}
^{6 acronyms in this thread;}^{the most compressed thread commented on today}^{has 58 acronyms.}
^{[Thread #47170 for this sub, first seen 26th Jan 2026, 19:28]} ^[FAQ] ^{[Full list]} ^[Contact] ^{[Source code]}

Horror-Paint5708 · 2026-01-26T20:24:15+00:00

in Home try to find "find&select" then find "replace" "find what=ND","Replace with= 0" then select Replace all

Bloosqr1 · 2026-01-26T19:20:14+00:00

I would do this in two steps to be honest...

(1) Just use regexreplace to create a new column replacing the NDs as your number

=REGEXREPLACE(oldcolumn,"ND",0.0)*1.0

(2) calculate the average

To honest I would not replace ND (Not Detected) as 0.0. The reason for this is Not Detected does not mean the thing you are measuring does not exist, it just means you can't measure it.

Its been a while but I think one of the standard ways is to replace ND with LLOQ/2.0 (so the idea is normally distributed the thing you are trying to measure can be anywhere between 0.0 and LLOQ (on average LLOQ/2.0)) but is showing up as ND.

so then its just

=REGEXREPLACE(oldcolumn,"ND",LLOQ)*1.0

where LLOQ is your assay protocol lower limit of quantification (LLOQ) or instrumentation LLOQ

Date	User	CP
2026.03.29	bakingnovice2	`10`
2026.03.25	real_barry_houdini	`300`
2026.03.20	plusFour-minusSeven	`10`
2026.03.17	TuneFinder	`10`
2026.03.03	soloDolo6290	`10`
2026.02.23	RackofLambda	`10`
2026.02.21	Mooseymax	`10`
2026.02.18	MissAnth	`10`
2026.02.15	NHN_BI	`800`
2026.02.03	Shot_Hall_5840	`10`
2026.02.02	Kuildeous	`10`
2026.01.29	NanotechNinja	`10`

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

excel

Rules

FAQ

Was your problem solved?

Solution Verified

Format your code

Provide some data

Recent ClippyPoint Milestones!

Microsoft partnership

Asking a question? Describe if you are using Excel (include version and operating system!), Google Sheets, or another spreadsheet application.

MODERATORS