Apache OpenOffice Community Forum

Hi

I have one column that may contain a positive or negative number and add it to another column that is an ongoing total.

I have inserted the normal sum and it works perfectly for positive numbers however, when I put in a negative number that should subtract from the ongoing total, it still treats it as an addition.

Can anyone tell me if there is a correct formula to insert that will solve this problem please?

Kind Regards

Peter

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

If the range for the entries is column B and there are 100 entries and they start on row 1, then the formula works fine, presuming that the negative numbers are being entered as negative numbers,
If this doesn't work for you, please attach a sample sheet containing your formula and some data so that we can see just what it is you are working with.
In order to attach a file (max 128k in size), click the Post Reply button, not the Quick Reply button, and the option to attach a file are closer to the bottom, under the reply screen.

I will try that thank you, Russel. So all I need to do is change the letter & numbers if necessary?

Correct.

File attached thank you

Hi Ziz

Just did it

Use the View - Value highlighting. This function to display the different data types in the cells by temporarly colorize them.

In my original post I see I made a blue. I should have said "row" not column. I'm a rank amateur at this computer caper, I'm sorry.

It's row 5 that is giving me trouble.

Kind Regards

Peter

You subctracted a negative number in the cell I5. It means in the algebra: you want add it as a positive number to the first operand.

It is not related to the original problem, but the dates have inconsistent format: A3 is a text, A4 is a real date value in your example file.

Hi Ziz

Your shooting way above my head, I'm afraid.

I didn't even know that it was a negative number.

All I'm trying to achieve is that if the number I place in I5 is a negative, it subtracts that amount from K5, if it is positive it adds that amount to K5.

What sum do I need to insert, and how should I type the number, i.e. with - in front of it?

Basically, I have to take into account if there's a losing day I can subtract that amount from the bank and if it is a winning day I need to be able to add it to the bank.

I don' understand why G4 and G5 are positive numbers and you are subtracting from them but G5 is negative and originally you were subtracting also?

What's the difference between the outcomes that the first two numbers are postive and G5 is negative.

It looks like you don't understand basic algebra. If you subtract a negative number from a positive number, the result will be greater than the positive number.
In column H you multiply two currencies, which is meaningless. But I suspect you formatted too many columns as currency.
The validation of column B uses column Q instead of column T (or S, in the second attachment).
The text in column B is invisible without value highlighting because the font and the background have the same colour.

Thanks, Alex.

No, I'm certainly not one of the world's great thinkers. I have come to databases very late in life, I do however understand what happens when you add a positive to a negative number.

My problem is I'm as dumb as a post on how and what I should put into a database to make it do what I want. I realise I've done something wrong, but as I'm only just trying stuff to achieve a result I don't know what to do. It's the old story, people who struggle with understanding different things with computers obviously frustrate those to whom it's second nature. I don't know if a cell is positive currency, or whatever. It's why I was seeking assistance. I'll just enter the figures manually with the aid of a calculator.

I thought it was a relatively simple question, maybe it's how I phrased it. You don't understand what I'm trying to do and I can't understand what people are telling me. Obviously I'm wrong and will take up no more of anyone's time.

Thank you to all who tried to assist, I do appreciate it.

Looking at the spreadsheet you attached, there is no data to show a loss day.
That being said, looking at the formulas you have, there is nothing that I can see that is wrong.
The only way that I can see a negative number showing in the calculations is if the entry in column G is greater than the product of the same rows in columns E and F (aka the entry in column H)
Even forcing that situation, the calculations in columns I and K still show correctly.
At the moment your entries for columns L and M are manually entered, rather than being calculated based on the data from columns E to G.

The only way that I can see a situation where you would be subtracting a negative number, as mentioned by jrkrideau and Alex1, is if columns E and G or F and G were both negative.
Based on the type of data that is being used, while I'm not a racetrack better, there is no manner in which I can see one of those situations occurring, since you'd have to have a negative investment in either case.

I think you may have hit on the nub of the problem and I think I can see what you are getting at.

I need another column with a negative sum (if that is the correct term) that interacts with column K. I thought that there maybe some way to do both from the one column. As I said, I know very little about formatting columns.

On a winning day you add the amount less the total you have invested to column K, and on a losing day you need to subtract that total from column K to keep a running total.

Would that work do you think please?

If the amount in column I is negative (so far in your actual data it's always positive), then your current formula for column K will work.
No need for another column.

The only ways that I can see you getting a negative number in column I are:
1) Odds (column E) is a negative
2) Investment (column F) is a negative
3) Total Days Investment (column G) is greater than the product of E and F

While Odds (which I've noted are being displayed in an unusual manner) can be against you, I've never seen negative odds (and if I ever did, I'd run from that bet/gamble as fast as I could)
You can't have a negative investment, as that is the amount you are betting on the horse for that race, thus option 2 is literally impossible.
Thus the only possible option, based on my knowledge, is number 3, and that would require your daily investment to be greater than the return as calculated by E and F

Hi

No, we were just using that as an example to test how to make a losing day. I won't go into my actual system for obvious reasons, it was just to test if instead of putting e.g 199 in column H, we put -199 just to test if that deleted an amount from column K

All the figures were just to test if it worked or not.

Good idea, just the wrong column for the entry.
Use column I for your negative entry and K will be updated correctly

It could work with an entry in H, but the mechanics of getting it to work are more difficult, since I is based on H

Thank you, Russel.

I've enough information to satisfy my simple needs and can consider this thread closed.

Thank you, again to everyone who offered help.