Hi,
Sorry for being away for a while. I'll now try to submit posts more regularly. in this post, i will discuss a worksrounf to dealing with error values while summing:
Let's say data is arranged in the following manner:
Name Marks
Ashish 20
Mahesh 30
Ravi 40
Suresh 50
One can simply use the sum function on column B to get a result. However, if Assume that instead of 50, you have an error value, say #DIV/0!. Now if you use the SUM() function, you will get a #DIV/0! error. To avoid this error, you haver to use the SUMIF() function. The SUMIF() function would be as follows:
SUMIF(B3:B6,"<>#DIV/0!"). What this formula essentially does is that it sums up all the non-error values in the range B3:B6. You may also want to go through the SUMIF() function in Excel's Help menu.
Now, let's take the problem a step further and assume that instead of 50 we have #DIV/0! and instead of 40 we have #N/A. Therefore, we now have 2 error values in the range B3:B6. The SUM() function would return #N/A. There are two workarounds to this problem:
Solution 1: SUMIF(B3:B6,">-9.99999999999999E+307"). What are are essentailly telling Excel to do here is that sum all values in range B3:B6 which are greater than the smallest number which can be input in Excel.
Solution 2: SUM(IF((NOT(ISERROR(B3:B6))),B3:B6)). Please note that this is an array formula (which is entered by pressing Ctrl+Shift+Enter instead of the conventional Enter).
Hope this information helps. Please feel free to post your Microsoft Excel queries here.