Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 178
Default Calling operators in formulas

Posting here since this group is much more active than the regular excel board.

Is it possible to call the 'operator' value of a cell as opposed to the 'text' value?

Example- A1 contains ''
A2 contains 2
A3 contains 1
I want to test =A2A3
Using the formula =A2&A1&A3 gives '21 instead of True. Is there some other way to do this?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Calling operators in formulas

Hi,

Am Fri, 21 Jul 2017 21:41:46 -0700 (PDT) schrieb Matthew Dyer:

Example- A1 contains ''
A2 contains 2
A3 contains 1
I want to test =A2A3
Using the formula =A2&A1&A3 gives '21 instead of True. Is there some other way to do this?


in B1 write:
=A2&A1&A3
Then select C1 and define the name "Result" refers to:
=EVALUATE(Sheet1!$B$1)
Now write in C1:
=Result


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 178
Default Calling operators in formulas

So I can define a name that encapsulates the 'formula' I want to evaluate, then use the evaluate formula to do the evaluation, but I cannot directly use 'greater than' instead of the text ''. I like the workaround
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default Calling operators in formulas


"Matthew Dyer" wrote in message

So I can define a name that encapsulates the 'formula' I want to evaluate,
then use the evaluate formula to do the evaluation, but I cannot directly
use 'greater than' instead of the text ''. I like the workaround


Yes EVALUATE is also Macro4 function which you can use in a named formula,
eg

=EVALUATE(Sheet1!$A$2&Sheet1!$A$1&Sheet1!$A$3)

Note where the cursor is when defining, depending on usage you may or may
not want the $'s in the named formula.

If all you want to do is cater for the 4 comparison operators

=IF(A1="",A2A3,IF(A1="=",A2=A3,IF(A1="<",A2<A3 ,IF(A1="<=",A2<=A3))))

With an additional IF could include "="

Easily adapted for 'greater than'

Could also be defined as a named formula

Peter T




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default Calling operators in formulas


"Peter T" wrote in message

Yes EVALUATE is also Macro4 function which you can use in a named formula,


Sorry, I misread Claus had already used Evalueate as a named formula

PT


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparison Operators in formulas Steve P[_2_] Excel Discussion (Misc queries) 1 November 6th 09 12:55 AM
Using Or and And Operators RyanH Excel Programming 2 August 26th 08 12:57 AM
Operators/if etc Peter[_61_] Excel Programming 4 December 18th 06 12:11 PM
Comparison Operators Devanath Ong Excel Programming 2 June 22nd 06 03:16 PM
Unary operators Joe Excel Worksheet Functions 3 February 14th 05 07:09 PM


All times are GMT +1. The time now is 09:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"