If i enter ">" in cell B1, then i want to test if A1 is greater than C1 (happy with just true or false result in say D1),
but if B1 is "<=" then test needs to whether A1 is less than or equal to C1,
etc etc etc...
rather than lots and lots of nested IF statements, is there an easy way to use the cell entry in B1 as the argument in a single IF function in D1 ?
Rich.
Hi,
Interesting question.
I don't think there are any standard functions that will allow you to do this so I'll be even more interested to see if there are. You could however use the following User Defined Function in the VBE
(LTest stands for Logic Test)
Please Login or Register to view this content.
In a cell enter
=LTest(A1,B1,C1)
where A1 and c1 are numbers and B1 is a logical expression.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Excel itself doesn't have this capability built in, but long I created a User Defined Function to do this sort of evaluation.
This UDF would need to be added to your workbook before you can use it as a formula in a cell.Please Login or Register to view this content.
How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook
The function is installed and ready to use.
Now the function only evaluates the text string found in a single cell.
If you have:
A1: 4
B1: >
C1: 3
You would next need to compile those values into a single cell. So put in this formula:
D1: =A1&B1&C1
Now you can use this new function:
E1: =RESOLVE(D1)
The added benefit of this function is that it will RESOLVE pretty much any equation fed into it, within reason.
Last edited by JBeaucaire; 01-06-2015 at 01:03 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks guys,
I was hoping to avoid VBE as it is a shared workbook (not everyone in the company applying appropriate security setings etc), but i like both of these solutions.
I'll give it a short time in case any other suggestions come along, and definitely use the Resolve module to expand other workbooks where this will save some space :-)
rich.
you should be able to use an old excel4 macro evaluate() but you can only call it via a named formula
in name manager
define a name as >>>>>>>> mycalc =EVALUATE(Sheet1!$A1&Sheet1!$B1&Sheet1!$C1)
see attached
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
That's awesome, Martin!
That's an excellent solution - thankyou for that!!
I've never even worked with a macro like that, using name manager,
Where do i find out about other such useful functions?
Rich
too be honest google! thats where i learnt it from. can't really post the link as it goes to another forum
but search for
excel get.cell excel 4
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks