How do I make a conditional IF statement(s) in a cell that will check about 10 or so ranges of numbers and return with a different number?
For example in cell F12:
IF F12 is equal to 3 or in the range of 2.2 to 3.3 then return the number 5 in the same cell.
It would need to repeat the check for 10 or so other ranges and return a different number so it would be a series of checks all concatenated.
Google was not my friend in this search.
Please help. Thanks
Instead of explaining how you might do it, but which doesnt work, could you give more detail about what you are trying to achieve. Preferably, as a spreadsheet with dummy content (or real content if confidentiality is not an issue) and cell comments describing target behavior.
A little about mindset: If you think of C programming as describing a process, a spreadsheet describes a state (or situation). A cell formula is not a statement, “what to do”, but rather a description, “how it is”.
Function IFS?
You can’t do this. Technically it woluld require a self-reference.
Generally self-references are not supported, and if Calc options are set to allow them for “iterations”, it wouldn’t serve your purpose. Having first returned th 5 e.g. the formulöa would find that this isn’t in the range foir that result, and check the conditions for a range containing 5.
If possible at all, you should try to find an unconditional expression (using ABS() and MAX() e.g as examples for functions internally checking for conditions) for what you want to get,
If this doesn’t work, make a little lookup table and try to use MATCH() and INDEX() or probably VLOOKUP().
The return is not in the same cell.
I’m not familiar with the expressions in the link provided.
I could do all of this in C using a switch statement but LibreOffice’s syntax befuddles me.
A clear example of a switch statement would be required if someone would be so kind as to offer.
All I have is this:
=IFS(F11<15,"10",F11<12,"11",F11<9.3,"12",F11<7.4,"13",F11<5.9,"14",F11<4.7,"15",F11<3.7,"16",F11<2.9,"17",F11<2.3,"18",F11<1.8,"19",F11<1.5,"20",F11<1.2,"21",F11<0.92,"22",F11<0.739,"23",F11<0.577,"24",F11<0.457,"25",F11<0.361,"26",F11<0.288,"27",F11<0.226,"28",F11<0.182,"29",F11<0.142,"30",F11<0.113,"31",F11<0.0091,"31",F11<0.0072,"32",1,"NA")
If I put a number above 15 it returns NA as it should. Any other number results in 10.
What am I doing wrong?
You misinterpreted what the function does. It returns the value for the first condition that evaluates to true, so for any value in F11 <15 it returns 10, and all other values are not matched by any condition so "NA" is returned. See also help on IFS function but even better summarized in the Function Wizard (hit Ctrl+F2 on your formula cell): “Checks 1 or more conditions and returns a value corresponding to the first true condition.”
Btw, to prevent misrepresentation of inline text formatting and replacements of quote characters with typographic quotes please format formula expressions as code using ``` triple backticks on separate lines before and after the code.
Maybe you can use the function LOOKUP.
F12: =LOOKUP(F11;D2:D26;E2:E26)
F12: =VLOOKUP(F11;$D$2:$E$26;2) also is valid here.
See sample file:
conditional_Alpha1ne_LeroyG.ods (16.9 KB). Note that the cell values in column C are shifted down one row. You can put the two column data in another sheet, or hide the columns.
I think I understand how to implement the LOOKUP function you’ve presented here.
I’m an old man and just got even older today.
I see that 24 is the formula and result cell with F11 the input.
I appreciate your time and effort to make such a solution, however it’s running backwards.
If I input 3.6 (amperage) then the permissible output must be 15, however it’s 16.
This is based on an AWG wire chart where the smaller number is larger gauge wire and a larger number is smaller wire.
I still don’t know how the voodoo that you do is in fact working, but I will read up more on LOOKUP again as I’ve gone through it a few many nights already but I got lost.
See line 2 above…
I you can help me understand how to fix the “running backwards” issue and how the LOOKUP works, I would be very grateful. Thanks.
When the value in [cell] F11 reaches a value in column D, it gives, in [cell] F12, the corresponding value in column E, until that, it gives the value of the previous row.
If do you select [cell] F12, pressing Ctrl+F2 opens the Function Wizard that give more hints.
You can rearrange the values. Copy and paste will work fine (if do you cut and paste, the formula in [cell] F12 will adapt and will not show the change).
By the way, seeing the chart, I supose that there must be a formula to obtain this values.
EDIT: I added “[cell]”, so as not to confuse it with the F12 key.
I don’t understand which values to switch.
I’m just not grasping the concept of the pairs.
The AWG chart limits are already set in the original IFS.
A far as the AWG chart, it’s simple.
Between amperage 3-4 the corresponding wire diameter is X.
For example #14 is able to safely pass 5.9 amps and less, but not more.
I also want to weed out larger wire sizes and use the bare minimum size-gauge, as no one uses larger wire to perform the job due to cost.
All this is something to do as a hobby not for commercial use.
I would evaluate various wire sizes I encounter in different products I’ve had a hand in, and plug in the values.
So I have a variable wire diameter from another equation result in a cell.
The next step is to find out what ampacity wire #X can carry and have that as an answer in a different cell.
In other words I have to show what #X wire size would be from the result in the other equation be it in decimal or metric and or ampacity.
An idle mind is not good as one gets older and mine is a little slower now.
A few different ways of explanation helps me quite a bit.
I hope you understand.
Here it is from #6 to #36
AWG 6-36.ods (25.7 KB)
One row up or down.
Column E in my first sample file (not needed now).
I added two formulas (each with three test values) to your list.
AWG 6-36_LeroyG.ods (21.7 KB)
Remember that
I just got in and it’s time for supper. I will have a look at your file and reply later.
Thanks for your help.
“I just got in…” I have been over tired trying to research a whole bunch of things. Unfortunately, I tend to miss my scheduled sleep times by 6 hours which causes all kind of internal clock errors. I said enough, and slept all day to do the make up!
Alert and awake, I see what you have done with the formula =LOOKUP(F12,$K$3:$K$33,$I$3:$I$33). You’re doing a comparison in a range between K3 to K33 and I3 and I33 for the input say, in F9. I haven’t been with it the past few days but it is crystal clear now. I had been searching online prior to setting up an account on this forum, seeking the answer but never found it. I couldn’t ask for a more clearer explanation of the syntax needed as offered by you! With your explanation of this formula and (sleep) the sample sheet visual I am extremely grateful for your help! Thank You! 