Hello - I have looked at documentation online till I’m blue in the face, and I’m missing something. I’m pretty sure what I want to do is fairly simple, however I just haven’t gotten it yet. So - here’s what I want to do.
I have a spreadsheet that the value of a given cell will be calculated depending on the value of one of two other cells in other columns. For example, there will be a numeric value in either cell C10 or cell F10, and the result will be in J10. If C10 has a number, one calculation will be performed for J10. If F10 has a value, J10 will have a different calculated result. There will never be numbers in C10 and F10. If both are blank, J10 should remain blank as well. Again, it seems like it’s pretty simple and straight-forward, but I apparently have a mental block! I started with, IF C10>10 … I’m able to do the calculations separately; in other words, I can have J10 show the correct result if I only have my formula in C10, OR, if I have my formula in F10. So, I need to combine the two, again, leaving J10 blank of both C10 and F10 are blank. Any insight would be greatly appreciated! HELP!!!
I do not fully understand why you started with the condition of >10, while your explanation talks about “empty” and “non-empyt” cells containing numeric values. In that sense the following IF statement may not exactly express what you really want to achieve:
=IF(C10<>"",C10,IF(F10<>"",F10,""))
PS: This formula introduces a precedence of C10 over F10, if there are - against your statement above - numeric values in both cells C10 and F10 as well.
Some people dont’t like nested IF statements due to their poor readability. You can also use IFS function:
=IFS(C10<>"",C10,F10<>"",F10,AND(C10="",F10=""),"")
Thank you for the reply. I appreciate it! SORRY!!! MY BAD!!! Rather than IF C>10, I meant to say, IF C>0 There will be a value/number in either C10 or F10, and, a calculation will be performed with the result in J10. There is a different formula/calculation for C10 and F10. In other words, for example, if the value in C10 is 10, the formula will result in “20” being in J10. The number in F10 could also be 10, but there’s a different formula, and the result in J10 would be “15.” If C10 and F10 are both blank, than J10 should remain blank as well. As stated, there will never be numbers in both C10 and F10; only one or the other, or neither. Hopefully this clarifies things? THANK YOU!!!
b.t.w. … I don’t mind nested IF’s at all - just would like it to work! 
What is unclear with my answer?. If you want anybody to provide a working formula for all of your real conditions, then you need to be absolutely precise in defining all of the conditions. Statements containing “for example” do not fulfill this requirement. This answse shows how it works and I assume you are able to put all of your conditions into one. with your additions “…there’s different formula” I still only show “how it works” by putting a “” into what is already shown (e.g. =IF(C10>0,,IF(F10>0,,"")).
OK - seems like I’m over-complicating it? - Here’s what I’m looking for:
IF(C10>0) THEN [J10=calculation-1] ELSE IF(F10>0) THEN [J10=calculation-2] ELSE J10=[blank]
J10 will only have a value if either C10 or F10 does. C10 and F10 will never both have a number
Thank you!
That’s what I gave you (forget about what you know about programming languages and conditional function for the moment)
=IF(C10>0,[put formula using c10 here],IF(F10>0,[put formula using f10],""))
This is how function IF does IF … THEN … ELSE. First argument is condition, second argument is what needs to be done if condition is TRUE and third argument is the ELSE part, if condition is FALSE. This is implicit execution of then & else without using the keywords THEN & ELSE. They do not exist in calc functions, which always have one keyword only, which is the name of the function.
Thank you! I’m still having trouble with the syntax of the statement. Here is what I tried, including the calculations, in J24:
=IF(C24>0,(C24-5.875),IF(F24>0,(F24/4-1.625),""))
If I put a number in C24, it does the correct calculation, however, if I put a number in F24, or leave both blank, J24 has #VALUE!
Also tried:
=IF(C24>0,(C24-5.875),IF(F24>0,(F24/4-1.625)," "))
I seem to get getting hung-up on the placement of the “(” and “)” and “,”
Also, is it a comma or semi-colon? I’ve seen both. Or does it not matter?
0
Thank you!
comma or semicolon - this depends on your locale. Since we are in an English forum, I have everything in en_US locale. But - me being a German - I’m used to have a colon “,” for decimal delimiter and a semicolon “;” to separate arguments in functions. This is one of the reasons why volunteers here answering questions start so often “Please tell as about your system…”. In short, you need to look for your locale settings. In my English installation your formula work.
Please note: “” (empty) is a different thing than " " (space). And the #VALUE! in general is an indication that you have no real number in the referenced cell, but a text looking like a number (if the value is alligned to the left, it is most likely a text).
My Columns, C, F, and J, are all formatted as Number, with three (3) decimal places. If there’s a number entered in Column C; in other words, IF C[row]>0 then J[row] should be (C[row]-5.875) If there’s a number in F[row]; in other words, IF F[row]>0, then J[row] should be (F[row]/4-1.625) If both C[row] and F[row] are blank, J[row] should be blank as well. I’ve tried many combinations, and have not been able to get the correct syntax with the “(” “)” and “;”
Please upload a test file - as said above, your formula is correct in the english locale. I cannot check yout installation (locale) nor can i check, whether your formula within your cells are the same as posted here.
And another hint: A cell being formatted as a number does not mean anything. If a number is not recognized as such, it will be a text and will stay a text. Formatting doesn’t automatically convert text ti number. Please see the alignment of your numbers - If they are left aligned, they are most probale text
Thank you for your help - It appears that I’m getting very close. I can now successfully calculate the correct value in Column J depending on what is in C or F. The one thing that I am still missing is how to have Column J blank if there is nothing in C or F. I copied my IF-statement/formula to all the rows in J, so they all have 0.000 I could manually blank them out, however, it would obviously be easier to have them blank, and only populate if there’s a value in C or F. I can upload a test sheet, however I’m not sure how to do that? Thanks again!
Why do you leave me so alone with my comments - you never refer to any of my statements and I don’t know whether any of 'em had any influence to your progress. Again here: I told you 4 comments above ** Please note: “” (empty) is a different thing than " " (space).** and I told you about numbers. If you now have 0.000 in cells you want to be “empty” then they are not empty (space character) and you have set “Conversion from text to number” as Treat as zero (see Tools -> Options -> LibreOffice Calc -> Formula -> cat: Detailed Calculation settings tick Custom and click button Details
Again as stated several times: Your formulas are correct but your format settings, locale and/or calculation setting do not match.
Thank you for your help! I finally got it. I do understand the difference between the “” and " " - I appreciate the clarification on that. Also, I appreciate the comments regarding #VALUE I found that merely deleting a number in a particular cell can cause #VALUE but actually clearing the whole content will leave the cell blank - I had never realized that before, and has caused me to become stuck in the past - thank you for that clarification!
With help and guidance from Opaque, I was able to find a solution.
Please do not use “Post Your Answer” if you actually don’t answer a question (in this case yours). Use “add a comment” function instead.
To demonstrate your question has been answered, click the check mark (
) next to the correct answer pr the answer which helped you to solve your question.