Excel Query

[Excel_77]

Hi All,

Below is an extract from a large file that I have. However, I have some problems with it:

1. I have added in the column "Total KWH" to add up the values of "UNR KWH" and "Day 1 KWH", however, I have an issue as some of the values appear to be minus with the - at the end (example 403.000-) so the formula does not pick this up, how can I address it?

2. I have to show the ID that has the highest kWh, how do I do this as some of the IDs have been duplicated?

Thanks in advance

 

[Sektor]

Assuming a value is in cell A1

=---SUBSTITUTE(A1,"-","")

 

[Excel_77]

Assuming a value is in cell A1

=---SUBSTITUTE(A1,"-","")

Thanks, I have just realised that this type of minus after the figure appears everywhere within the document, it there a fast way to move it from the end to the beginning or is there a way for the formula to detect it at the end of the figure?

 

[Sektor]

You could check the last character, and if it's minus, the coercion formula is applied, otherwise original number is returned:

=IF(RIGHT(A1)="-",---SUBSTITUTE(A1,"-",""),A1)