Set result on condition with IF()

[OPENOFFICERULES]

hello i was looking for guidance with the following: i am working on a tracker and we have some percentages for example between 40% - 49.99% and the person with that percentage shall get a $1 comm, so i need something that will tell me X person is on X percentage range and will get 1 usd in return highlighting the fields as well. i believe it can be done with an IF function and conditional formatting but i cant get it through tho, can i get a little help please? would be appreciated

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[John_Ha]

Welcome to the forum.

Go to Calc. Have a look at Calc Functions - you are looking for IF which is a Logical Function.

Also check Calc Tutorials and, of course, everything can be found in the Calc Guide.

[crusader]

Uploading an example will allow others to more accurately understand your question and offer practical guidance. Please be sure to remove sensitive data: a few examples should be adequate.

[OPENOFFICERULES]

my first time posting sorry.
heres an example:
i have chart "A" with a comp plan for sales commissions
36.00% - 37.99% $0.25
38.00% - 39.99% $0.50
40.00% - 49.99% $1.00 } what agent will get per sale made if his/her conversion falls within this range
another chart of course tells me the total amount of sales in cell E9 and total conversion so far in cell D9, what i need is to have chart "A" highlighted depending on the agents conversion and the amount of money he/she will get again based on the conversion rate
i was trying something like =IF(D9>=40.00%=<49.99%, .... of course its just an example and its wrong but just to give you a better idea. in the end i need another cell calculating the total amount of money that will be given to that agent.

thank you in advance.

[Villeroy]

IF is the most overestimated spreadsheet function.

[OPENOFFICERULES]

not sure tho but it doesnt highlight

[John_Ha]

Villeroy

I'm not sure I completely understand how your short expression works . For easier understanding might it be better to use the full expression

Code: Select all

=LOOKUP($A1;$I$1:$I$4;$J$1:$J$4)

rather than your shortened 

=LOOKUP($A1;$I$1:$J$4)

Also, it is well worth reading Help to see why it works.

LOOKUP
Returns the content of a cell either from a one-row or one-column range or from an array. Optionally, the assigned value (of the same index) is returned in a different column and row. As opposed to VLOOKUP and HLOOKUP, search and result vector may be at different positions; they do not have to be adjacent. Additionally, the search vector for the LOOKUP must be sorted in ascending order, otherwise the search will not return any usable results.

If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion.

The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text string that is also a regular expression, you must precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in Tools - Options - OpenOffice Calc - Calculate.
Syntax
LOOKUP(SearchCriterion; SearchVector; ResultVector)
SearchCriterion is the value to be searched for; entered either directly or as a reference.
SearchVector is the single-row or single-column area to be searched.
ResultVector is another single-row or single-column range from which the result of the function is taken. The result is the cell of the result vector with the same index as the instance found in the search vector.

Example
=LOOKUP(A1;D1:D100;F1:F100) searches the corresponding cell in range D1:D100 for the number that you entered in A1. For the instance found, the index is determined, for example, the 12th cell in this range. Then, the contents of the 12th cell is returned as the value of the function (in the result vector).

not sure tho but it doesnt highlight

Apply the highlight separately. It is often much easier to use "working cells" to do intermediate calculations etc, and then apply the result. The column(s) with the working cells can then be hidden. This is especially useful if you use multiple IF and AND functions to do what you want.

[Villeroy]

I'm not sure I completely understand how your short expression works . For easier understanding might it be better to use the full expression

Always used to work like this in Excel and the same behaviour has been implemented by Kohei in 2007: https://bz.apache.org/ooo/show_bug.cgi?id=74245
When the second argument is a 2D array, LOOKUP performs a vertical lookup if the search array is taller than wide or square.
It performs a horizontal lookup if the search array is wider than tall.
Search vector is the first column or row respectively.
The return value comes from the last column or row respectively.

In this attachment I used the MATCH function for the conditional formatting. In default mode with 2 arguments it works exactly like LOOKUP but with a vector (1-dimensional row or column) returning the matching position number.

[John_Ha]

Villeroy

Thanks for the explanation. I am, as I have said on a number of occasions, a bear of little brain. I guessed that was what was happening with your (I think) undocumented shortcut but I had no idea about the check for width vs height.

Similarly, I hate recursive programming - I just get tangled into knots with it. I like it verbose with easy-to-follow small steps.

[Villeroy]

Thinking twice about this feature, I noticed a glitch.
Open the attached document. The formula in A15 looks up the random number in A14 in A and returns the matching value from column I. The formula in A16 does the same using the 3-parameter notation.
Now insert a new column between B and I. Content does not matter. Both formulas adjust accordingly returning a result from column J now.
Insert a second column and the short version fails with #N/A because a vertical lookup turns into a horizontal one.

[John_Ha]

Similarly, I hate recursive programming

But I do like the dictionary definition of recursive ...

recursive: see recursive

[Bald Eagle]

Maybe take a look at this:

viewtopic.php?f=75&t=92044