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

HansVogelaar

MVP

Oct 28, 2022

NZViPeR

1) Perhaps

=IF(DONO.csv!G3="","N/A",DONO.csv!G3)

2) For example:

=AVERAGE(--SUBSTITUTE(D5:D8,">",""))

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