[Solved] Potential limitation on cell formulas?

Discuss the spreadsheet application

[Solved] Potential limitation on cell formulas?

Postby Engineer817 » Sun Dec 29, 2019 2:51 am

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.
Last edited by Engineer817 on Mon Dec 30, 2019 12:20 am, edited 2 times in total.
Open Office V 4.1.5
Windows 7 Professional 64
Engineer817
 
Posts: 7
Joined: Thu Feb 01, 2018 9:34 pm

Re: Potential limitation on cell formulas?

Postby FJCC » Sun Dec 29, 2019 4:01 am

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Potential limitation on cell formulas?

Postby RusselB » Sun Dec 29, 2019 4:36 am

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Potential limitation on cell formulas?

Postby Engineer817 » Sun Dec 29, 2019 4:37 am

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

What do you mean by helper columns?
Attachments
Finances.ods
(16.63 KiB) Downloaded 20 times
Open Office V 4.1.5
Windows 7 Professional 64
Engineer817
 
Posts: 7
Joined: Thu Feb 01, 2018 9:34 pm

Re: Potential limitation on cell formulas?

Postby RusselB » Sun Dec 29, 2019 5:02 am

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Potential limitation on cell formulas?

Postby RusselB » Sun Dec 29, 2019 5:23 am

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Potential limitation on cell formulas?

Postby FJCC » Sun Dec 29, 2019 5:42 am

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   Expand viewCollapse view
=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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Potential limitation on cell formulas?

Postby Engineer817 » Sun Dec 29, 2019 5:47 am

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.
Open Office V 4.1.5
Windows 7 Professional 64
Engineer817
 
Posts: 7
Joined: Thu Feb 01, 2018 9:34 pm

Re: Potential limitation on cell formulas?

Postby MrProgrammer » Sun Dec 29, 2019 6:25 am

RusselB wrote: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!.

Engineer817 wrote: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.

Engineer817 wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3962
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Potential limitation on cell formulas?

Postby Engineer817 » Mon Dec 30, 2019 12:15 am

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   Expand viewCollapse view
=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.

FJCC wrote: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   Expand viewCollapse view
=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.
Open Office V 4.1.5
Windows 7 Professional 64
Engineer817
 
Posts: 7
Joined: Thu Feb 01, 2018 9:34 pm


Return to Calc

Who is online

Users browsing this forum: fiona87, FJCC, Google [Bot] and 19 guests