Formulas, cell content

Discuss the spreadsheet application
Post Reply
OldFool
Posts: 3
Joined: Thu Mar 23, 2017 4:46 pm

Formulas, cell content

Post by OldFool »

Gentlemen, Ladies,

I am writing formulas for a small business accounting system. I need a formula in D10 to be recognized as having no value to a formula in cell G10. Currently the formula in G10 sees the formula in D10 as a value and returns a 0.00.

The formulas are as follows;
Cell D10. =IF(ISNUMBER(SEARCH("PayPal";CashReciepts.F11));CashReciepts.D11*0.032;"") This returns a blank cell to the viewer when the entry "PayPal" is not present in cell F11 of the CashReciepts journal sheet.

Cell G10. =IF(ISNUMBER(SEARCH("Fuel";F10));"";IF(ISNUMBER(SEARCH("Freight";F10));"";IF(ISNUMBER(SEARCH("License";F10));"";IF(ISNUMBER(SEARCH("PayPal";F10));"";IF(E10>0;E10*0.07;""))))) This returns a 0.00 in lieu of a blank cell as intended.

Also if possible, I would like any entry on a row in the CashReciepts sheet to be printed in the next available row on the CashDisbursements sheet. As it now is written, an entry in the CashReciepts sheet returns an entry on the adjacent row in the CashDisbursements sheet. The issue is that this row may have data already entered.

Any thoughts will be appreciated,
Terry
Windows 10, OpenOffice 4.1.2
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formulas, cell content

Post by Zizi64 »

Please upload your real .ods type example file here...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
OldFool
Posts: 3
Joined: Thu Mar 23, 2017 4:46 pm

Re: Formulas, cell content

Post by OldFool »

Calc.ods, I am unsure as to how I go about uploading a file?? I could attach it to an email but have never uploaded a file on a forum.
Windows 10, OpenOffice 4.1.2
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Formulas, cell content

Post by RoryOF »

The upload tab is below the Submit button on the PostReply of FullEditor screens (not on the QuickReply screen).
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formulas, cell content

Post by Zizi64 »

The file size limit is 128 KiB.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formulas, cell content

Post by keme »

OldFool wrote:Gentlemen, Ladies,

I am writing formulas for a small business accounting system. I need a formula in D10 to be recognized as having no value to a formula in cell G10. Currently the formula in G10 sees the formula in D10 as a value and returns a 0.00.
Creating an accounting system in a spreadsheet is an accident just waiting to happen. Please tell us that you do the actual bookkeeping in a proper accounting software, and use spreadsheets for analysis of existing data only!
The formulas are as follows;
Cell D10.

Code: Select all

=IF(ISNUMBER(SEARCH("PayPal";CashReciepts.F11));CashReciepts.D11*0.032;"")  
This returns a blank cell to the viewer when the entry "PayPal" is not present in cell F11 of the CashReciepts journal sheet.

Cell G10.

Code: Select all

=IF(ISNUMBER(SEARCH("Fuel";F10));"";IF(ISNUMBER(SEARCH("Freight";F10));"";IF(ISNUMBER(SEARCH("License";F10));"";IF(ISNUMBER(SEARCH("PayPal";F10));"";IF(E10>0;E10*0.07;""))))) 
This returns a 0.00 in lieu of a blank cell as intended.
...
The formula you have given for G10 does not reference D10 anywhere, only E10 and F10.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
OldFool
Posts: 3
Joined: Thu Mar 23, 2017 4:46 pm

Re: Formulas, cell content

Post by OldFool »

Guys,
I am an uneducated 78 year old retired fellow who runs a one man lawn care mowing business. About $8500 USD yearly revenue, about 7-8 customers. The accounting is quite simple, there used to be manual input/posting of data, Cash Receipts journal and Cash Disbursements journal. So, I thought why not create a spread sheet?? Please see the attached file to view the entire attempt at this project.

It all has gone well for the last three years and produced decent results until people began paying me through PayPal. I thought I could automate the cost of PayPal fees by having the Disbursements sheet fill in the cost of doing business with PayPal when money is received and posted in the CashReciepts journal. Normally when I write a formula that sees a zero value in a cell the formula returns a blank cell to the viewer. An example is the end of the formula contained in G10 of the attached file. IF(E10>0;E10*0.07;"") When I use this formula in a cell that requires a manual input and no value is present, the formula returns a blank cell. The thinking is if E10 is greater than 0 do the calculation, if False, then nothing. But for some reason I cannot get the same result when the cell being referenced contains a formula such as E10 has in the attached file.

Looking at the formula contained in cell G10, =IF(ISNUMBER(SEARCH("Fuel";F10));"";IF(ISNUMBER(SEARCH("Freight";F10));"";IF(ISNUMBER(SEARCH("License";F10));"";IF(ISNUMBER(SEARCH("PayPal";F10));"";IF(E10>0;E10*0.07;""))))) it returns a 0.00 in lieu of the blank cell I expected. Ahh, I have been playing with this and found if the formula continues to look at cell F10 in lieu of changing the reference to E column, it returns a blank cell. Formula changed to =IF(ISNUMBER(SEARCH("Fuel";F17));"";IF(ISNUMBER(SEARCH("Freight";F17));"";IF(ISNUMBER(SEARCH("License";F17));"";IF(ISNUMBER(SEARCH("PayPal";F17));"";IF(F17>0;E17*0.07;""))))) now returns a blank cell as desired.

However, it would be nice if when a PayPal reference is made in the CashReceipts sheet, the CashDisbursements sheet would go to the last entry row and post the data on the next row below?? As it is written now, it will post the data where ever the formula happens to state a specific row. Many years ago I worked with Excel and functioned as an inside sales person for a hydraulic and pneumatics component distributorship. I often helped to design fluid power systems for different types of machines. In this sheet there was a running tally of jobs and when a new input was typed into one sheet, it would go to the last entry of a second sheet and record the data. I cannot remember how I did this, but it worked well and saved time and prevented errors.

I do have issues with anything academic, it is why I do manual labor, I have a great memory, it is just very short unless the subject is mechanical.

Thank you all for your patience,
Terry, Just an oldfool trying to survive.
Attachments
2017Cash-ReceiptsJournal-01.ods
(50.93 KiB) Downloaded 78 times
Windows 10, OpenOffice 4.1.2
Post Reply