Hi,
I’d like to create a function in Calc that matches a name ie J Smith or M Person in one cell and than checks another cell to see if it is greater than zero and if the second is greater than zero it will give a set value in a specified third cell.
IE The formula is in J1 and checks to see if B1 is J Smith or M Person, E1 is checked to see if it is 0 or greater and if so than J1 tells me the value in M1 or M2 which is a number attached to J Smith or M Person
The most i can do is =IF(E27>0,12)
In your question the mention of “M1 or M2”, and also “greater than zero” vs. “0 or greater”, are ambiguous.
I am assuming that the choice of M1 or M2 depends on the name being J Smith or M Person, respectively, and that only positive values in E1 should yield a match.
Note that using two rows for one entry in a table is generally a bad idea. It will mess up sorting, filtering and aggregation (by SUMIF()/AVERAGE() and similar functions, as well as pivot tables). Not sure whether that applies here (i.e. whether “M2” in your question belongs to “this entry” or “next entry”. I thought it best to mention it anyway, just in case.
If the name check is - and will always be limited to - only two specified names, you can use this “intuitive” formula:
=IF(E1>0;IF(B1="J Smith";M1;IF(B1="M Person";M2;"No match"));"No positive")
A more likely scenario is with a list of names which may change over time, but that does not fit my assumption above very well. A table lookup function is the way to go for that (perhaps MATCH() )
Also, if B1 is a keyed-in user entry we are comparing to, some cleanup may be required. The TRIM() function is useful for that.
Note that this is intended as a preliminary step, to show a possible solution and hopefully help you to help yourself.
If this is a solution, or showed you how to arrive at a solution by your own, click the “tick bullet” to the left to mark the issue as solved.
Comment on your question, or on this answer, (“Add a comment” below) if the proposed solution does not address your situation.
The formula is for a time and travel log for house cleaning and has 7 names and is used to list the distance if I spend more than 0 hours at one person’s place.
Basically column A is date, column B is the Name of the person Columns C & D are the fees & charges Column E is the hours spent. Column F is the total cost paid, H and I are for notes relating to other costs on my behalf and I’d like J to list the distance from my place to the house I’m cleaning as listed in a separate sheet.
Your function has pointed me in the right direction I think. If I can’t get it working I’ll just stick with copy pasting the IF function I posted above.