Forum Discussion
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 its not keep the value from G2 but everyway i try it does not work
secondly in another cell i want to import data from 4 cells in the same sheet and display it as an average but some of the numbers in the workbook im importing from have > in that instance id like it to assume its just the number for example >5 becomes 5
Regards
Mark
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),">",""),""))
9 Replies
1) Perhaps
=IF(DONO.csv!G3="","N/A",DONO.csv!G3)
2) For example:
=AVERAGE(--SUBSTITUTE(D5:D8,">",""))
- 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 5If 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,">",""))
