[Solved] Potential limitation on cell formulas?

[Engineer817]

Hello, I'm a bit of a novice at calc still and I could use some help

I'm designing a spreadsheet for use with personal finances, the purpose being to allocate percentages of a check to certain categories in an effort to budget a bit more effectively.

This is the formula I am using for each cell

=IF(OR(WEEKNUM(TODAY(); 1) >= A3 ; (YEAR(TODAY()) <> A1)) = 1; (E2 +(B3 *V3)-F3 ); "")&T(STYLE(IF(OR(WEEKNUM(TODAY(); 1) >= A3 ; (YEAR(TODAY()) <> A1)) = 1;"Green";"Black"))) `Working
=IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A1)) = 1; (E3 +(B4 *V3)-F4 ); "")&T(STYLE(IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A1)) = 1;"Green";"Black"))) `#VALUE!

I know the formula works, I've been working on it all day weeding out issues and it checks out as doing what I want it to do on the first cell, but on the second it comes out with a #VALUE! error in any sequential cells. I think I've narrowed it down to the fact that each cell's calculation is dependent on the cell above it. Removing the preceeding cell (Changing E3 in the second formula to a constant number) makes the formula work as intended, but my application requires that dependancy.

I don't really know how to proceed from here to be honest.

[FJCC]

Please upload a small example file showing the problem. To post a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.

[RusselB]

Your formulas, as given, should work properly if all of the requirements match.
Typing a formula into the forum can lead to typos, where you get something that looks correct, but doesn't exactly match the actual formula.
For this reason, along with others, I agree that a sample of your spreadsheet, with the layout being correct (actual data is irrelevant, but helpful if you can supply dummy data), is needed.
If, for some reason you won't or can't supply a sample (there are ways of making the data anonymous), then my next suggestion would be to split your formula into sections and use helper columns.

[Engineer817]

Here is the working project
Columns E and F are my current testing area

What do you mean by helper columns?

[RusselB]

Helper columns are extra columns that can be used to break down a complicated formula into smaller sections.
I'll look at your attachment and see what I can do with it... I may upload a version of your sample using helper columns... depends on just how complicated it turns out to be.

[RusselB]

You have no entries in column F for the rows where you are getting your #VALUE error, since your formula requires data from column F of that same line.

[FJCC]

I think the problem is that you are concatenating the result of the T() function to the result of the preceding IF(). That makes the result into text and the subsequent cell cannot do a calculation using text. The Style function returns zero. Try adding that to your IF() result as in the example below.

Code: Select all

=IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A2)); (E3 +(B4 *$V$3)-F4 ); "")+STYLE(IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A2));"Green";"Black"))

I also removed the test of OR() = 1. The OR() function returns either TRUE (1) or FALSE(). You do not need to test whether it is equal to 1.
Finally, I made the reference to V3 absolute, writing $V$3, so the formula can be copied conveniently.

[Engineer817]

Thank you for the replies, I'll test them come morning.

I did try removing the entire stylizing section of the formula to no result, but I will try your solution, thank you.

[MrProgrammer]

You have no entries in column F for the rows where you are getting your #VALUE error, since your formula requires data from column F of that same line.

No, that is not the problem. Cell E5 wants to use the value of cell F5 in a subtraction operation. F5 is empty. Cell F5 is treated as having a value of zero and subtraction will work. As a test, temporarily put formula =9-F5 in cell H5. This will produce 9, not #VALUE!.

I think I've narrowed it down to the fact that each cell's calculation is dependent on the cell above it.

No, that is not the problem. Cell E4 contains text, not a number. As a test, temporarily put formula =ISTEXT(E4) in cell D4. Cell E5 uses E4's text value in an arithmetic operation, and you receive a #VALUE! error.

Cell E4:
=IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A1)) = 1; (B2 +(B4 *V3)-F4 ); "")
&T(STYLE(IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A1)) = 1;"Green";"Black")))

This formula has two functions, IF() and T(), joined by the & operator. The & operator always returns text. =ISTEXT(E4) is TRUE.

You are using the STYLE function when you should be using Format → Conditional Formatting. Simplify your formula by removing everything after the & operator so that it can return a number. Well, when the IF test is false it will return the null string, but this value is treated as zero when you use it with an arithmetic operator. Format these cells with style Black.

Put your formatting test in a helper column: =OR(WEEKNUM(TODAY(); 1) >= An; (YEAR(TODAY()) <> A1)). Then test the helper in the conditional formatting dialog: Condition 1 → Formula is → HelperCell and conditionally apply cell style Green. Read about this feature in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum. The helper column does not have be visible. You can use Format → Column → Hide or you can put it far to the right, say column ZZ.

Cell E4 produces a (text) result and does get a #VALUE! error because the formula there is different than all the other cells in column E. (I'm not showing the part after the & operator.)
E3: =IF(OR(WEEKNUM(TODAY(); 1) >= A3 ; (YEAR(TODAY()) <> A1)) = 1; (E2 +(B3 *V3)-F3 ); "")
E4: =IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A1)) = 1; (B2 +(B4 *V3)-F4 ); "")
E5: =IF(OR(WEEKNUM(TODAY(); 1) >= A5 ; (YEAR(TODAY()) <> A1)) = 1; (E4 +(B5 *V3)-F5 ); "")
E6: =IF(OR(WEEKNUM(TODAY(); 1) >= A6 ; (YEAR(TODAY()) <> A1)) = 1; (E5 +(B6 *V3)-F6 ); "")
Note that E4 uses B2 before the + operator. The others use a cell in column E.

It seems that you want to use $V$3 in your formula instead of V3 so you can fill this formula down. Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know. The conditional formatting formulas also support relative, absolute, and mixed references.

I see that FJCC has suggested a way to change your formula so STYLE() will produce numbers. A minor problem with =IF()+STYLE() is that you will get 0 in the cell instead of "" when the OR() test fails since the + operator always returns a number. There are ways to write the formula to get "" insead of 0 in those cases but I'm not going to provide further details because you really should use conditional formatting for this task.

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.

[Engineer817]

Thank you FJCC for the help, I found a couple of errors in the code you posted, mostly in the style section, and modified a couple more things, ending with this:

Code: Select all

=IF(OR(WEEKNUM(TODAY(); 1) >= A3 ; (YEAR(TODAY()) <> $A$1) ; (WEEKNUM(TODAY(); 1) = 1)); (E2 +(B3 *V3)-F3 ); "")+STYLE(IF(OR(WEEKNUM(TODAY(); 1) >= A3 ; (YEAR(TODAY()) <> $A$1); (WEEKNUM(TODAY(); 1) = 1));"Green";"Black"))

The additional or condition is to help with this week in particular, as its week 1 of 2020, but still 2019.

Also thank you for the $ tip, I had been using notepad++ column editor to build each columns code in mass, using some nifty features, but with the ability to make certain variables absolute, that saves me a lot of work.

I think the problem is that you are concatenating the result of the T() function to the result of the preceding IF(). That makes the result into text and the subsequent cell cannot do a calculation using text. The Style function returns zero. Try adding that to your IF() result as in the example below.

Code: Select all

=IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A2)); (E3 +(B4 *$V$3)-F4 ); "")+STYLE(IF(OR(WEEKNUM(TODAY(); 1) >= A4 ; (YEAR(TODAY()) <> A2));"Green";"Black"))

I also removed the test of OR() = 1. The OR() function returns either TRUE (1) or FALSE(). You do not need to test whether it is equal to 1.
Finally, I made the reference to V3 absolute, writing $V$3, so the formula can be copied conveniently.