excel numberformat...

Question

i am using excel 2007... i have the following ranges...

      D    E       G   H
  5   1    2       1   1
  6   2    2       1   1

now i select the range F8:G9 and write the following formula "=D5:E6*0.2+G5:H6*0.3" and press the CTRL+SHIFT+ENTER... i.e. doing matrix addition,... now i have the following result

       F    G
  8   0.5  0.7
  9   0.7  0.7

now i select this result range and change its format of the cells to number format to number with 0 decimal places.... now i have the following result

     F  G
 8   1  1
 9   1  1

now i have c# program which will read this range values...(with range(...).value2)... then will try to find the distinct values but c# assumes that it reads it the above case 1,1,1,1... which is not correct in the reality. moreover c# program will now use these distinct values to format the cells with colors by using the equals operator in which case none of the cell values is equal to 1...

is it possible in excel to make that change after numberformat,... to the real value2 of the cell... but not just appearance! because when i ask for example

MsgBox Range("F8").Value2

it shows me 0.5, but since i have changed the cell format i want to see 1 instead... I do not want to round the value of Range("F8").Value2, since it will work only for specific case.

my question is how it is possible to change the F8:G9 range's values after numberformat to the ones that numberformat has changed them????

thanks!!!

Answer 1

The formatting is just a presentation issue as to how the data is displayed. If you want to work with rounded values you should round the actual value, either in Excel using the round function, or in .net using Math.Round.

Answer 2

Use the Text property of the Range object to return the formatted version

e.g. if cell A1 contains 0.77458 and is formatted to one decimal place then

• Range("A1").Value2 = 0.77458 (as a Double value)
• Range("A1").Text = 0.8 (as a String value)