[Solved] Please help with my formula!!

[ScooterB]

Hello, I'm working on a spreadsheet that is really doing some simple things. But I am writing it for some folks who are not necessarily the most savvy. So I am setting up some cells to do evaluation for them. These cells will tell them whether all of four column totals match the total of a single other column. Then I have a cell next to the preceding cell that will say "Missing" or "Good".

Now, right now, one of my cells is working perfect. It does the math AND applies a STYLE to it. But the other cell, which is dependant on the value of said cell, isn't working so hot. Here is the formula that I have in the cell that works:

Code: Select all

=IF(F49+G49+H49+I49<>D50; D50-(F49+G49+H49+I49); 0)+STYLE(IF(CURRENT()<>0; "ORANGE"; "GREEN"))

Here is the code of the cell which ISN'T working, but dependant on the value of the other cell:

Code: Select all

=IF(I50<>0; """"missing""""; """"good"""")+STYLE(IF(I50<>0; "ORANGE"; "GREEN"))

Now, if I separate the evaluation formula and put it in a single other cell, that cell behaves correctly and says "Missing" or "Good". And, if I put the STYLE equation in yet another cell again, that works just fine. It has to do I think with combining the two processes. But why doesn't the other cell freak out when combining the two equations???!!!!!

If I need to make a screenshot then let me know if that would help. I guess we can upload screenshots can't we??

Anyway, thanks for your help in advance and have a great day!!


SOLVED::: acknak was correct in that I could do what I needed by using Conditional Formatting. I don't have the text in there but they at least change colors which is a visual key !!! Thank you acknak!

[RoryOF]

Almost always a sample .odt/.ods etc file is of more use than a screenshot, as it allows checking of the exact structure, using the exact data.

[ScooterB]

Thanks RoryOF, I'll try to upload one post haste.

[ScooterB]

Ok Rory....How do I actually upload a Calc spreadsheet?? I don't see that option. Just the one for showing the code.

Thanks!

[RoryOF]

The upload tab is below the submit button on the PostReply and FullEditor screens, not on the QuickReply screen.

[ScooterB]

I found it about the time that you answered my question. Thanks!!

[ScooterB]

Here it goes again...

[acknak]

Two suggestions off the top of my head.

First, drop the STYLE part of the formula. That sort of formatting is better handled through conditional formatting (Format > Conditional Formatting > ...). You can apply a specific cell style based on the value in the cell, or on the result of a formula. This (at least) simplifies your formulas and may avoid some of the instability you've encountered.

Also, I wonder if your formula is doing what you expect. Maybe this instead:
=IF((F49+G49+H49+I49)<>D50; D50-(F49+G49+H49+I49); 0)

[ScooterB]

That part is working ok. It's the cell to the right of the one with that formula in it. The one that is to the right, is supposed to just evaluate if cell I50 is 0 or not. If it is zero, it says zero and makes it green. If it isn't zero, then I need it to do the math and turn orange. Like I said, if I separate the two formulas, into two different cells, then those other two cells work like I need them to. But when I combine the two formulas, it freaks out. But then I wonder why the actual formula that is doing the math AND applying the style works!!!???

[RusselB]

In the formula that isn't working, you have too many quotation marks in the first IF section.
You can reduce the formula in H49 to

Code: Select all

=C49-SUM(E48:H48)+STYLE(IF(CURRENT()<>0; "ORANGE"; "GREEN"))

The (somewhat) working formula for I49 is

Code: Select all

=IF(H49<>0;"missing";"good")&STYLE(IF(H49<>0;"ORANGE";"GREEN"))

I say, somewhat, due to the fact that the formula gives an extra 0 after the word missing, and when forcing the value of H49 to 0, the style colour doesn't change.

I'm not familiar with the STYLE function, so this may be due to a lack of knowledge regarding how that function works.

[ScooterB]

Now that you guys can see the spreadsheet, the formula in H49 works just like I need it to. it's the one in I49 that isn't. I also noticed that some of the styles didn't import correctly when I copied it into a test spreadsheet. But you guys probably get the idea anyway....

The formula in I49 I can split it up and put the math evaluation in some other cell and it will show MISSING or GOOD.

I can also take the STYLE function and put it into yet another cell and it will turn orange or green based upon the value in H49. It's the combining of the two.

But am I not doing the same in H49? It is actually having to do math and then apply a STYLE. All I want from I49 is to look at the value in H49, put ether "MISSING" or "GOOD" and then change the colors.

Here's the gig; if you actually play with putting X's in some of the columns, you will see that H49 value changes. When it says zero, I want it and the cell adjacent to it to turn green. I also want the cell (I49) to say "GOOD". Now, when you have H49 and C49 NOT equal (meaning that the totals at the bottom of each column do not equal the total of all the names entered) I need it to say "MISSING" because the value in H49 will be doing the math and telling them that have missed putting an X in one of the four columns.

Gosh, I hope I got that all to make sense.....lolll

[MrProgrammer]

But when I combine the two formulas, it freaks out.

By "it freaks out" you presumably mean that the cell shows #VALUE! so it would be more helpful for you to say that directly instead of making us guess the meaning of your vague "it freaks out". Please use sentences like "But when I combine the two formulas, the cell displays #VALUE!." Remember that you are asking for free assistance so you should be as clear as possible in your statements. This is the twelfth post in the topic. If you'd explained the problem clearly and attached your document as explained in Survival Guide for the forum, you could have gotten your problem solved with the first reply.

=IF(I50<>0; """"missing""""; """"good"""")+STYLE(IF(I50<>0; "ORANGE"; "GREEN"))

But then I wonder why the actual formula that is doing the math AND applying the style works!!!???

I believe you intend your first IF function to return the text missing or good. But your quotation marks are incorrect. You have four of them when you want one (or possibly three). After fixing that, you have an addition of the result from the first IF function and the STYLE fuction. The way you've written it, IF returns text; STYLE returns 0. You can only add numbers, not text and a number. The result of trying to do arithmetic with text is #VALUE!. Read section 1. Types of data of Ten concepts that every Calc user should know.

That sort of formatting is better handled through conditional formatting (Format > Conditional Formatting > ...)

Agreed, but if you want to use STYLE try =IF(STYLE(IF(I50; "ORANGE"; "GREEN"));;IF(I50; "Missing"; "Good")).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[ScooterB]

@Me Programmer, it is true that WE ALL are volunteers and can assist others if we have the knowledge. However, I didn't know that there was a counting of posts in a thread. For your information, I did look at the Survival Guide, but you didn't even ask. You just chastised me....

Now, Mr Programmer, the formula that you posted above is supposed to do what? There isn't any evaluation being done (i.e., A condition that would force a decision being made) so how would that be if use?

I come here to learn and help if possible. Everyone is a newb at some point. Try not being so harsh....

[ScooterB]

And for everyone else: Thank you for your assistance. I'll figure it out somehow.

[RusselB]

@Me Programmer, it is true that WE ALL are volunteers and can assist others if we have the knowledge. However, I didn't know that there was a counting of posts in a thread. For your information, I did look at the Survival Guide, but you didn't even ask. You just chastised me....

Now, Mr Programmer, the formula that you posted above is supposed to do what? There isn't any evaluation being done (i.e., A condition that would force a decision being made) so how would that be if use?

I come here to learn and help if possible. Everyone is a newb at some point. Try not being so harsh....

The IF(I50 in MrProgrammer's code is a condensed binary comparison. If I50 is 0, then the binary comparison returns a FALSE level evaluation. If it's anything else, then it returns a TRUE level evaluation.
Another way of writing it would be

Code: Select all

 =IF(STYLE(IF(I50<>0; "ORANGE"; "GREEN"));;IF(I50<>0; "Missing"; "Good"))

NB: I think MrProgrammer accidentally used row 50 rather than 49 in the code.

[ScooterB]

@RusselB - Thank you for clarifying that. I am much more used to seeing it written that way. I was forced to be using the Microsoft product for a very long time and am new to the world of Open Office.

I do have a question though... In between the two formulas you wrote you are using semicolons. What do they signify? Are they more like a placeholder which causes the cell to evaluate the first formula and then the next in order? I can definitely see that by using the + sign it would throw the formula off like Mr Programmer said; trying to do a math function on text and numbers doesn't work..

But I still don't see how the one doing the actual math. Someone mentioned that I was missing a closing parenthesis and sure enough, when I looked at I was. But the crazy part is that when I went in and added the parenthesis, it made the equation stop functioning. I took it out, and it started working again.

Thanks for your help! I will try the formula that you gave me and go from there. Have a great weekend!

[RusselB]

While you may have been missing a closed parenthesis, the exact placing of the missing parenthesis can make the difference between a formula that works, one that doesn't work and one that works, but returns an incorrect response.

As to the usage of semicolons, in OpenOffice they are used to separate parameters in a function.
Excel uses the comma for the same purpose.

[ScooterB]

That makes sense...Thanks!!