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

1) awesome that worked a treat
2) My bad by same sheet i meant to say from DONO.csv sorry
in another cell I want to import data from 4 cells from DONO.csv and display it as an average but some of the numbers in the workbook I'm importing from have > in that instance id like it to assume its just the number for example >5 becomes 5

HansVogelaar

MVP

Oct 28, 2022

NZViPeR

If the cells are in a single block:

=AVERAGE(--SUBSTITUTE(DONO.csv!D5:D8,">",""))

If they are scattered:

=AVERAGE(--SUBSTITUTE(DONO.csv!A2,">",""),--SUBSTITUTE(DONO.csv!B4,">",""),--SUBSTITUTE(DONO.csv!C6,">",""),--SUBSTITUTE(DONO.csv!D8,">",""))

NZViPeR
Copper Contributor
Oct 28, 2022
HansVogelaar
Hey mate tried this
=AVERAGE(--SUBSTITUTE(DONO.csv!R2:W2,">",""))
im getting #VALUE!

=AVERAGE(--SUBSTITUTE(DONO.csv!R2:W2,">","")(--SUBSTITUTE(DONO.csv!R2:W2,"CALL","0"))
Does this look right?. Any ideas why im getting #VALUE!

Regards

Mark
- 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),">",""),""))