Convert Scientific notation into Decimal values.

The formula was to calc the diff between send and recieve times where they were both >0
otherwise put "" .. this way its ignored by the xl AVERAGE Function; that data is in Column T and the =AVERAGE(T2:T26877) is in cell T1.Please see the spreadsheet attached.Please help me write the formula for above requirement in the spreadsheet attached and convert all the columns with scientific notation to decimal values,your help is highly appreciated.

Dump.csv
zonate_toasterAsked:
Who is Participating?
 
CluskittConnect With a Mentor Commented:
SUM(<range>)/AND(COUNTIF(<range>,"<>0"),COUNTIF(<range>,"<>"""""))

That should simply add them and not take into account whether it's a 0 or empty.
0
 
zonate_toasterAuthor Commented:
Calculation for average is performed using column P and  Q for column T(Q-P).
0
 
Robberbaron (robr)Commented:
1/ You posted a csv file, not an excel format.... it just has data within.

2/ the scientific notation within CSV is automatically converted to a "number" when csv opened in excel. In an excel file, you still see the Exponent format only  because the cell width is small. Widen the cell and it shows 1280000000000000000

3/ All your time values are the same in the CSV... 1280000000000000000; also no need to send so much data,  5 lines would often be enough for testing.

4/ I believe the Formula Column T you are looking for is

=IF(P2>0,IF(Q2>0,Q2-P2,""),"")

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
CluskittCommented:
You don't need to change cells to "" in order to correctly calculate an average. Just use SUM(<range>)/COUNTIF(<range>,">0")
0
 
Robberbaron (robr)Commented:
except that zero may be  a valid value to include in the average.  
Same for negative , though unlikely in this case.
0
 
CluskittCommented:
He did say at the beginning that he only wants to take into account values that are >0.
0
 
Robberbaron (robr)Commented:
"both >0"   they could be equal....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.