Use custom message

[Pat]

The following formula will return TRUE or FALSE. How do I modify it to
return a custom message like:

"Updated" for true
"Not updated" for false

{=SUMPRODUCT(1*(ControlCentre!$C$77:$C$1000=Purchases!BM25))>0}

Much appreciate any help.

Pat

 

[JE McGimpsey]

there's no need to array-enter this:

=IF(SUMPRODUCT(--(ControlCentre!$C$77:$C$1000=Purchases!BM25)),
"Updated","Not updated")

 

[Peo Sjoblom]

I noticed that you have curly brackets in your example, sumproduct can be
entered normally

=IF(SUMPRODUCT(1*(ControlCentre!$C$77:$C$1000=Purchases!BM25))>0,"Updated","Not
updated")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Aladin Akyurek]

Also, wouldn't it be less costly if you invoke instead...

=COUNTIF(ControlCentre!$C$77:$C$1000,Purchases!BM25)

the cell of which you could custom format as:

[=0]"Not Updated";[>0]"Updated"

 

[Pat]

Also, wouldn't it be less costly if you invoke instead...

I am interested by your comment. I presume by using your approach less
demand would be placed on excel. If this is so, how can you tell what
formula is causing a greater demand on the system than using an alterative
method (if an alternative formula is available)?

Also, wouldn't it be less costly if you invoke instead...

=COUNTIF(ControlCentre!$C$77:$C$1000,Purchases!BM25)

the cell of which you could custom format as:

[=0]"Not Updated";[>0]"Updated"

The following formula will return TRUE or FALSE. How do I modify it to
return a custom message like:

"Updated" for true
"Not updated" for false

{=SUMPRODUCT(1*(ControlCentre!$C$77:$C$1000=Purchases!BM25))>0}

Much appreciate any help.

Pat

 

[Aladin Akyurek]

A few rules of thumb...

1] Formulas that must operate on array-objects (like the ones that
require control+shift+enter, formulas with SumProduct, etc.) are
generally slower than equivalent formulas that operate on range objects.
They behave more so when they are applied to huge ranges in substantial
number of cells.

2] Formulas with volatile functions like INDIRECT, OFFSET, etc. tend to
prolonge re-calc times.

3] Lookup formulas with the match-type set to 0 are slower than lookup
formulas with the match-type set to 1 (that is,
LOOKUP(...),MATCH(...,1), VLOOKUP(...,1), etc.).

You can find more on the subject at: http://www.decisionmodels.com/

Also, wouldn't it be less costly if you invoke instead...

I am interested by your comment. I presume by using your approach less
demand would be placed on excel. If this is so, how can you tell what
formula is causing a greater demand on the system than using an alterative
method (if an alternative formula is available)?

Also, wouldn't it be less costly if you invoke instead...

=COUNTIF(ControlCentre!$C$77:$C$1000,Purchases!BM25)

the cell of which you could custom format as:

[=0]"Not Updated";[>0]"Updated"

The following formula will return TRUE or FALSE. How do I modify it to
return a custom message like:

"Updated" for true
"Not updated" for false

{=SUMPRODUCT(1*(ControlCentre!$C$77:$C$1000=Purchases!BM25))>0}

Much appreciate any help.

Pat