How to compute spread and % difference between highest and lowest value in 2 columns?

[gopnik]

Here is the deal, i have 2 columns in a google sheet doc with a bunch of values and #error ! and #ref !

I use

=$B:$B=max(IFERROR(B:B))
and
=$C:$C=min(IFERROR(C:C))

to highlight the lowest value in C and the highest value in B, and ignore errors


Problem 1= how to compute the spread and the % difference between the highest B and lowest C value?


Problem 2= these datas are JSON datas imported through importJSON commands, but come in various numerical forms messing up my sheets and biaising the formulas.


The problem here is the comma vs dot
how can i uniformize the values in comma format only?


Thanks

 

[gopnik]

here is how it looks for now
a mess isn't it

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 124px"><col width="123"></colgroup><tbody>[TR]
[TD]777.00023347[/TD]
[TD]781.99998999[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]#ERROR![/TD]
[TD="bgcolor: #F3F3F3"]#ERROR![/TD]
[/TR]
[TR]
[TD]798.618000,2,2.000[/TD]
[TD]793.850000,1,1.000

[/TD]
[/TR]
[TR]
[TD="width: device-width,, bgcolor: #F3F3F3"]#ERROR![/TD]
[TD="width: device-width,, bgcolor: #F3F3F3"]#ERROR![/TD]
[/TR]
[TR]
[TD]785.95000000[/TD]
[TD]794.45300000[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]#REF![/TD]
[TD="bgcolor: #F3F3F3"]#REF![/TD]
[/TR]
[TR]
[TD]788.89[/TD]
[TD]788.98[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]#ERROR![/TD]
[TD="bgcolor: #F3F3F3"]#ERROR![/TD]
[/TR]
[TR]
[TD]751[/TD]
[TD="bgcolor: #B7E1CD"]760,00003[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]771,53498[/TD]
[TD="bgcolor: #F3F3F3"]766,01002[/TD]
[/TR]
[TR]
[TD="bgcolor: #F4C7C3"]816[/TD]
[TD]816,99[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]#REF![/TD]
[TD="bgcolor: #F3F3F3"]#REF![/TD]
[/TR]
[TR]
[TD]#REF![/TD]
[TD]#REF![/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]#ERROR![/TD]
[TD="bgcolor: #F3F3F3"]#ERROR![/TD]
[/TR]
[TR]
[TD]#ERROR![/TD]
[TD]#ERROR![/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]742,65[/TD]
[TD="bgcolor: #F3F3F3"]785,28[/TD]
[/TR]
[TR]
[TD]760[/TD]
[TD]789,99[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]789.00[/TD]
[TD="bgcolor: #F3F3F3"]789.29[/TD]
[/TR]
[TR]
[TD]786,79008[/TD]
[TD]786,79008[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]790[/TD]
[TD="bgcolor: #F3F3F3"]791,7176643[/TD]
[/TR]
[TR]
[TD]790.63216607[/TD]
[TD]792.61362124[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]#REF![/TD]
[TD="bgcolor: #F3F3F3"]#REF![/TD]
[/TR]
[TR]
[TD]777.8000[/TD]
[TD]805.4900[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]787.6,1.19[/TD]
[TD="bgcolor: #F3F3F3"]792.82,2.1734[/TD]
[/TR]
[TR]
[TD]777,54[/TD]
[TD]789,95[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]#REF![/TD]
[TD="bgcolor: #F3F3F3"]#REF![/TD]
[/TR]
</tbody>[/TABLE]