EXCEL FORMULA

[philipmck]

= IF (A2288<>"", IF (RAW_DATA!B2288 > RAW_DATA!B2287, (RAW_DATA!B2287 + (50000–RAW_DATA!B2288), (RAW_DATA!B2288 – RAW_DATA!B2287) * 0.22)),"")

I have this formula in my database to do a calculation but it brings up an error box stating that " the formula i typed contains an error". What i am trying to do is to create a formula to do the following:

If (x1 > x2, = x2 + (limit - x1), x2 - x1)

In english this would be :

If x1 > x2
Then x2 + (limit - x1)
Else x2 - x1

PS thanks for your help in advance

 

[GlennUK]

=IF(A2288<>"",IF(RAW_DATA!B2288>RAW_DATA!B2287,RAW_DATA!B2287+(50000-RAW_DATA!B2288),(RAW_DATA!B2288-RAW_DATA!B2287)*0.22),"")

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

Brackets were the issue but I also noticed that when I pasted the formula into excel, the - wasn;t a minus sign but a dash....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[philipmck]

Yes I am having a problem with that as when i run the formula it is bringing back a minus figure, something it is not meant to do. Would you know why this is and i am using the formula that GlennUK suppled.

Thanks for your help (xlbo & glennuk)

 

[GlennUK]

What are the values in RAW_DATA!B2288 and RAW_DATA!B2287?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

dunno - what data is in RAW_DATA B2287 & B2288?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[philipmck]

They are values that come in from PHD. One for instance i think is 193.88 or a number like that but they most definately shouldnt appear in negative. I know that for a fact. Its just getting the correct formula is the problem.


\\FORMULA//
=IF(A2288<>"",IF(RAW_DATA!B2288>RAW_DATA!B2287,RAW_DATA!B2287+(50000-RAW_DATA!B2288),(RAW_DATA!B2288-RAW_DATA!B2287)*0.22),"")

 

[philipmck]

I have also noticed that when i used my number it was minus approx 40000 and now using glenns formula it is down to approx 5000 but it should be positive

 

[philipmck]

it should read about 100-200 or something around that

 

[xlbo]

Well it is YOUR formula. Only YOU can tell us what is meant to happen. If you tell us what is in the cells then we can see what the formula is doing but you need to decide on the logic. As far as I can see, your formula is doing exactly what you stated in your logic with the exception of the *0.22....we don;t know your needs - we can only work off what you tell us....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[philipmck]

The *0.22 is because it comes in as m3 in raw_data and in formatted data it needs to be mgal. this converts it.

what it is meant to do is find out if the cell that it is inserting into the database is greater in size that the one previous which is already there. if this is the case it then subtracts 50000 from the cell being imported and adds it onto the previous cell. if the cell being imported is smaller in size it then takes the previous cell which is already there away from the cell being imported. this is all it is meant to do.

 

[xlbo]

so new value is in B2288 and current value is in B2287?

if this is the case it then subtracts 50000 from the cell being imported and adds it onto the previous cell

In which case, this bit:
RAW_DATA!B2287+(50000-RAW_DATA!B2288)

should be:
RAW_DATA!B2287+(RAW_DATA!B2288-50000) if you want to take 50000 AWAY from the value in B2288


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[philipmck]

this is still not working correctly for whatever strange reason.

this is the formula which it currently uses:

=IF(A2287<>"",IF(Raw_Data!B2287-Raw_Data!B2286>=0,(Raw_Data!B2287-Raw_Data!B2286) * 0.22,F2286),"")

but what i need to change it for is because i need to add in limits ie the 500000 is a limit. so that when it reaches the limit it resets itself to zero and starts again hence the reason it subtracts 50000.

this is my formula at the minute

=IF(A2297<>"",IF(Raw_Data!C2297>Raw_Data!C2296,Raw_Data!C2296+(Raw_Data!C2297 - 50000),(Raw_Data!C2297-Raw_Data!C2296)),"")

 

[xlbo]

what sets itself to 0? We don't know what's going on inside your head. You are being very unclear about what it is you are trying to do - only in this last post have you mentioned anything about RESETTING to 0!

Lets say that B2288 holds a value of 52000 and the old value is 45000. What would be your expected result? - please state all logic used to get to it

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[philipmck]

IF(A2287<>"",IF(Raw_Data!B2287-Raw_Data!B2286>=0,(Raw_Data!B2287-Raw_Data!B2286) * 0.22,F2286),"")

What the above formula is doing is finding if 2287 - 2286 is >= 0. if so thanks ok if not use the previous cell (2286)

i was given

if (x1 > x2, = x2 + (limit-x1),x2-x1)

and then i have to make it work. this is what the formula below is but i doesnt seem to as im always getting a negative response

=IF(A2297<>"",IF(Raw_Data!C2297>Raw_Data!C2296,Raw_Data!C2296+(Raw_Data!C2297 - 50000),(Raw_Data!C2297-Raw_Data!C2296)),"")

 

[xlbo]

Until you answer my question about the result you would expect based on the numbers given, I'm not going to be able to help you

The formula you have got works exactly as the logic you have stated. How about you go back to whoever gave you that formula and ask them to make sure it is actually what they want because:

=IF(A2297<>"",IF(Raw_Data!C2297>Raw_Data!C2296,Raw_Data!C2296+(Raw_Data!C2297 - 50000),(Raw_Data!C2297-Raw_Data!C2296)),"")

Is the correct syntax for the logic of:

if (x1 > x2, = x2 + (limit-x1),x2-x1)

If its not giving you correct answers then the logic is wrong in the 1st instance



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

You keep saying that you can't get a negative answer, but you are, in fact, guaranteed a negative answer anytime "x1 > x2" is true and x1 > limit.

Consider if x1 = 50, x2 = 25 and Limit = 10. Your original formula with these values would read:
[tab]=IF(50>25,25+(10-50),25-50) = IF(50>25,25-40,25-50) = 25-40 = -15

But I think a bigger issue is what seems to be a communication problem in this thread.

philipmck - are you running your posts through an online translator to come up with English? Or is English a second language? It's totally fine if so, no big deal - we can try to work around it. But if you are fluent in English, then you really need to slow down and ask your questions in a more straightforward way.

Regardless, you need to stop using jargon (what is PHD, M3 or MGal?) and remember that:
[tab]* We aren't familiar with what you're working on
[tab]* We only know what you tell us
[tab]* We can't see what you see
[tab]* we don't know what you're thinking

So you need to figure out what you want the formula to do, and THEN ask a clear question. You've changed courses two or three times during this thread.

I think it might help if you phrase your question in plain language rather than guessing at formulas you don't seem to understand. But what you ask for must be logical.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

One more question:

i was given

if (x1 > x2, = x2 + (limit-x1),x2-x1)

and then i have to make it work.

Given by whom?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lionelhill]

Right people, let's turn on the psychology, and have a guess at what's happened:

(1) The answer is probably that someone has miswritten the formula you're trying to do, philipmck. It should probably be if(x1<x2, =x2+(limit-x1), x2-x1).

(2) The thinking is as follows:

I assume the data are a list of measured volumes in a tank. If the tank becomes filled, it is emptied, and then filling continues as normal. Limit is the volume of the tank.

The formula is supposed to tell you the volume added since the last measurement.

Therefore if the current measurement is larger than the last, all you need to do is subtract the previous measurement from the current. If x2>x1 answer = x2-x1.

If the current measurement is smaller than the last, we assume the tank has been emptied, so the answer is what's in the tank at the moment (x2) plus what was needed to top up the previous measurement to a full tank (limit-x1).

(3) If you're worried about why I've swapped the direction of the < sign, look at Excel help on the syntax of =if()

(4) Note that there is a small weakness in the logic. If someone fills the tank twice between measurements, you will get the wrong value. But this is a fundamental fault of methodology that can't be fixed by Excel. You are assuming a big tank and frequent measurements, which may be perfectly valid.

 

[philipmck]

Thank you all.