Forum Discussion

Copper Contributor

Oct 28, 2022

Solved

Please Help!!

Hi I am new to excel but I'm learning all the time using google and youtube. My problem is I'm trying to bring data in from another sheet so I'm using =DONO.csv!G3 then if its blank Display N/a if it...

excel

HansVogelaar
Oct 29, 2022
NZViPeR

Assuming that you don't want to include CALL in the average:

=AVERAGE(IFERROR(--SUBSTITUTE(SUBSTITUTE(R2:W2,"CALL",""),">",""),""))

This will result in #DIV/0! for a row with all CALL. If you want to suppress that error:

=IFERROR(AVERAGE(IFERROR(--SUBSTITUTE(SUBSTITUTE(R2:W2,"CALL",""),">",""),"")),"")

If you want to count CALL as 0:

=AVERAGE(IFERROR(--SUBSTITUTE(SUBSTITUTE(R2:W2,"CALL",0),">",""),""))

NZViPeR

Copper Contributor

Oct 28, 2022

so ive figured out that I'm getting #VALUE! because some of the lines have call
=AVERAGE(--SUBSTITUTE(DONO.csv!R2:W2,">",""))*AND(--SUBSTITUTE(DONO.csv!R2:W2,"CALL","0")) ive obviously done this wrong
below is a sample of the data
CALL CALL CALL CALL CALL CALL
>5 CALL CALL >5 CALL CALL
CALL CALL CALL CALL CALL CALL
CALL CALL CALL CALL CALL CALL
CALL CALL CALL CALL CALL CALL
CALL CALL CALL CALL CALL CALL
>5 CALL CALL CALL >5 CALL
>5 >5 CALL CALL >5 CALL
>5 5 >5 >5 >5 >5
4 4 CALL CALL CALL CALL
CALL CALL CALL CALL CALL CALL
1 CALL CALL CALL 1 CALL

regards mark

HansVogelaar

MVP

Oct 29, 2022

NZViPeR

Assuming that you don't want to include CALL in the average:

=AVERAGE(IFERROR(--SUBSTITUTE(SUBSTITUTE(R2:W2,"CALL",""),">",""),""))

This will result in #DIV/0! for a row with all CALL. If you want to suppress that error:

=IFERROR(AVERAGE(IFERROR(--SUBSTITUTE(SUBSTITUTE(R2:W2,"CALL",""),">",""),"")),"")

If you want to count CALL as 0:

=AVERAGE(IFERROR(--SUBSTITUTE(SUBSTITUTE(R2:W2,"CALL",0),">",""),""))

NZViPeR
Copper Contributor
Oct 29, 2022
perfect mate
altered it slightly to get the data from the other workbook
=AVERAGE(IFERROR(--SUBSTITUTE(SUBSTITUTE(DONO.csv!R12145:W12145,"CALL",0),">",""),""))
is there away to take the answer and for anything with decimal places after the first number round it up i.e. 1,3333333 rounded to 2 and 0.345333 rounded to 1
- HansVogelaar
  MVP
  Oct 29, 2022
  NZViPeR
  
  Like this:
  
  =ROUNDUP(AVERAGE(IFERROR(--SUBSTITUTE(SUBSTITUTE(DONO.csv!R12145:W12145,"CALL",0),">",""),"")),0)
  - NZViPeR
    Copper Contributor
    Oct 30, 2022
    thankyou so much for your help I really appreciate it