=SUM function that returns text result

[trodneyrotter]

Hi,

I have a spreadsheet, not written by me, that has an error checking process built into it.

This works by using sub-checks to sum a range of cells which if there are no errors return 0 and if there are errors return 1.

The bit I am interested in understanding, is there is an SUM function which returns the result 'ok'.

The main error check is a SUM of the other SUMs and if it is 0 across the workbook, returns 'ok'.

Does anyone know how this is done. I can see how an IF function would return a text result based on numbers, but not sure how this is working.

The main error check is a SUM of the other SUMs and if it is 0 across the workbook, returns 'ok'.

Thanks,

Ryan

 

[Special-K99]

There's either an IF function somewhere producing it or possibly a VBA macro.

 

[Gilliam]

how about nesting the sum function in the logical test of an IF?
=IF(< Your SUM Function > = 0,"OK", "Not OK")

 

[Jonmo1]

The SUM function itself cannot return a text string "ok"
But you can use the IF to test if the SUM = 0
=IF(SUM(...)=0,"ok","Not ok")

The thing to consider when testing if the sum is 0 for the purpose of verifying all values are 0 is negative values.
If there are negative values in the range that offset the positive values, then the test is invalid.
-5 0 0 0 5
Sum = 0, but not all values are 0.

If that's a possibility, then you need to actually count the 0's (or count NON 0's).

=IF(COUNTIF(range,"<>0")=0,"ok","not ok")

 

[Peter_SSs]

As already stated, the SUM function cannot return text. But you could custom format your 'SUM' cells to show text. For example, each of the cells in row 4 below has a simple SUM formula summing the 3 cells above. The row 4 cells are custom formatted as follows:

"Not OK";"Not OK";"OK"

Excel Workbook
	A	B	C
1	2	1	-5
2	3	2
3	-5	-2	5
4	OK	Not OK	OK
OK

 

[trodneyrotter]

Thanks everyone for your ideas. Peter pointed me to looking at the cell formatting, which is formatted using a custom format of [Magenta]"Err";[Magenta]"Err";[Blue]"OK".

As already stated, the SUM function cannot return text. But you could custom format your 'SUM' cells to show text. For example, each of the cells in row 4 below has a simple SUM formula summing the 3 cells above. The row 4 cells are custom formatted as follows:

"Not OK";"Not OK";"OK"

OK

	A	B	C

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 69px"><COL style="WIDTH: 71px"><COL style="WIDTH: 72px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-5[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]OK[/TD]
[TD="align: right"]Not OK[/TD]
[TD="align: right"]OK[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula A4 =SUM(A1:A3) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4