Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 860
  • Last Modified:

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
0
zonate_toaster
Asked:
zonate_toaster
  • 3
  • 3
1 Solution
 
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
 
CluskittCommented:
You don't need to change cells to "" in order to correctly calculate an average. Just use SUM(<range>)/COUNTIF(<range>,">0")
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
CluskittCommented:
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now