Search for numbers depending the presence of another cell.

Discuss the spreadsheet application

Search for numbers depending the presence of another cell.

Postby Kryonox » Sun Feb 17, 2019 9:52 pm

I am really above my head this one, so much I cannot find a proper Subject title for it.

I tried to explain it as good as I could in a screenshot.

When entered 'Y' in cel Y103 (In this case - or any other of the blanc cells above in the same column to the right of 'End of Day') then the formula which appears in cell AA107 below 'Result' must search for the first cell below 'Budget' in column Z that contains a number. This depends on an entry in column X under P/L.

Then that same formula must search for the first cell containing 'Result' (in the same column) with a percentage number below to it.

Then the formula uses those two numbers to calculate the Result of the Day. That calculation I can do.
I just cannot find a way to search for those two non-empty cells. One below 'Budget' and the other below 'Result' ...
Match? offset? vlookup? ....pffff?

I really would thank you so much
OpenOffice 4.1.6
Windows 7 64bit
Posts: 4
Joined: Tue Feb 12, 2019 10:05 pm

Re: Search for numbers depending the presence of another cel

Postby RusselB » Sun Feb 17, 2019 10:05 pm

This could be easily done using a lookup function, but that would require a restructure of your spreadsheet.
All of the duplicate headings causes a lot of trouble for something like this.
If you had just one set of headings, then all of the numbers in the column with blanks only where numbers aren't applicable, this would be a lot easier.
Can you upload/attach your actual spreadsheet, rather than a screenshot? It would enable me, and anyone else looking at this, to determine what, if any, other problems would arise and allow us to see the actual formula you are currently using.
OpenOffice 4.1.6 and LibreOffice on Windows 7 Pro & Ultimate
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.
Posts: 5021
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Search for numbers depending the presence of another cel

Postby Kryonox » Wed Feb 20, 2019 6:09 pm

Hi, thank you so much for your time

I attached the actual shortened version of the spreadsheet.
So, what you see is an Calc spreadsheet to follow up trades on an exchange
It is a shortened version ...
this is NOT only a logbook, but a LIVE follup up of the trades.
Mostly multiple trades at once at a high tempo and quick execution/calculation is required.

The setup is as follows:
there are 5 major column-groups, from left to right
– left side, date and share
– the buy column
– the calculation column
– the sell colum
– and results column

I wanted to have everything from ONE trade in ONE block of rows, for easy copy/paste for another trade … it is always unclear how many trades will be made in a day. Therefore I choose to type an 'Y' (or anything else) in a specific cell, to trigger the 'End of Day'

It is also possible that a share isn't sold, so the results column stay blanc for that trade.
But trading continues … with next buy orders. So the next block get filled with buys/sells
therefore, as long as that share isn't sold, and the results stay empty, the next trade must consider the last trade made before that unsold share.

– it most still be possible to insert an extra line for a 5th buy order (or 6th …)
this I can do now by copying row labeled '3' and paste on row label '4' with 'shift cells Down'
an example is Trade 7 (share ggg)

-All formulas are in one block (precedents/dependents) of rows.
Except: (and that is the problem)
'Budget' which must search the previous non-empty 'Budget' in the same column, higher up.
And 'Result' which must find the previous 'Result' (End of Day) in the same column, higher up.

-It must be possible to do all this on the fly while live trading....

So, what functions /formulas are best suited for resolving the 'Budget' and 'Result' problem?

Thank you for your time and effort ...
(36.82 KiB) Downloaded 17 times
OpenOffice 4.1.6
Windows 7 64bit
Posts: 4
Joined: Tue Feb 12, 2019 10:05 pm

Re: Search for numbers depending the presence of another cel

Postby UnklDonald418 » Tue Feb 26, 2019 10:03 pm

A few comments on your spreadsheet.
One approach to solving the problem with Budget in column Y is to do the calculations in a separate column or even a separate sheet. For simplicity, I added formulas to column AF
Cell AF2 use =Y2
Cell AF9 use =W9+AF2
Cell AF16 use =W16+AF9
Cell AF23 use =W23+AF16
Cell AF30 use =W30+AF23
Since W23 is 0, the results in AF16 and AF23 display the same value, allowing the ensuing calculations to display the correct values.
Then in column Y
Cell Y9 use =IF(INT(F9)-INT(Q9)=0;AF9;0)
Cell Y16 use =IF(INT(F16)-INT(Q16)=0;AF16;0)
Cell Y23 use =IF(INT(F23)-INT(Q23)=0;AF23;0)
Cell Y30 use =IF(INT(F30)-INT(Q30)=0;AF30;0)
Mixing potential data types in a single cell can lead to problems, so in the Else clause I used the number 0 instead of an empty text string "" when there are unsold shares.

As far as .
Must be possible to easy and quick add a line, keeping all formulas

You could try a recorded macro.
But in reality what you have is a database. While simple databases can be implemented using a spreadsheet, yours may be a little too complex for a spreadsheet.
For instance
must be possible to easy and quick add a line, keeping all formulas

with a database the buy/sell transactions could be displayed in a table control on a form. Table controls can have a navigation bar and scroll bars so if there are more transactions than will fit in the display box the scroll bar will allow them all to be accessed.
This line has a conditional format and turns Yellow, to establish an 'End of Day

would be unnecessary using a database, you could step through the transactions on a form one day at a time.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice - Windows 10 Professional
Posts: 1125
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Return to Calc

Who is online

Users browsing this forum: Majestic-12 [Bot], OfficeNewbie and 10 guests