formula problem

[steve111]

hi

=IIf([text28]=0,[qtyreqd]-[qtyrecd],[qtyreqd]-[qtyrecd]+[text28])

the formula is not giving me the negative or positive results I should get
for example

qtyreqd 53
qtyrecd 53
text28 1 ( rejected qty)

should give me an answer of -1
but I get 1
if I increase the qtyrecd to 60 I get -6 yet I want 6

if I decrease the qtyrecd to 10 I get 44

I think it is to do with the {text28] as the operator will enter a positive number this maybe should show a negative number
can this positive been turned into a negative number within the formula
any help appreciated

steve

 

[Mile-O]

Multiply it b -1.

 

[Brianwarnock]

Multiply it b -1.

Is that too simplistic, he would get -44 when the value is 10 . He needs to decide what he is trying to achieve and do the arithmetic correctly
I.e. Maybe it should be qtyrecd-qtyreqd

Brian

 

[steve111]

hi

=IIf([text28]=0,[qtyreqd]-[qtyrecd],[qtyreqd]-[qtyrecd]+[text28]*-1)

qtyreqd =53
qrtrecd = 50
text28 = 2

balance = 1

should be 5

still lost with it

steve

 

[Brianwarnock]

Mile-o meant

=IIf([text28]=0,[qtyreqd]-[qtyrecd],([qtyreqd]-[qtyrecd]+[text28])*-1)

Note the brackets

Brian

 

[Mile-O]

Okay let's look at this:

=IIf([text28]=0,[qtyreqd]-[qtyrecd],[qtyreqd]-[qtyrecd]+[text28])

Where Text28 =1, qryreqd = 53 and qtyrecd = 53

So:

=IIf(1=0,53-53,53-53+1)

So, 1 does not equal 0, therefore we are looking at 53-53+1. So, 53-53 = 0 and then +1 = 1.

If you want -1, then it's an issue of brackets. 53 - (53+1) = -1.

=IIf([text28]=0,[qtyreqd]-[qtyrecd],[qtyreqd]-([qtyrecd]+[text28]))

 

[Mile-O]

Another point to consider is why do we even have an IIf() here?

If Text28 (that name!!!!!) is equal to 0 then qtyreqd - (qtyrecd + Text28) will still be qtyreqd - qtyrecd as qtyrecd + Text28, where Text28 = 0, is just qtyrecd.

 

[Brianwarnock]

..He needs to decide what he is trying to achieve and do the arithmetic correctly
[/ QUOTE]

I stand by my statement of post 3, and he needs to remember BODMAS

Brian

 

[steve111]

hi

formula for qtyreqd

=[Supplier parts Ordered].[Form]![qty reqd]

formula for qtyrecd

=[Parts booked into stores].Form!totalqty

formula for rejected( text28)

=IIf([Parts booked into stores].[Form]![Passed]="Rejected" Or "part rejected",[Parts booked into stores].[Form]![text23],"0")

formula for balance

=IIf([text28]=0,[qtyreqd]-[qtyrecd],[qtyreqd]-[qtyrecd]+[text28]*-1)

steve

 

[Mile-O]

=IIf([Parts booked into stores].[Form]![Passed]="Rejected" Or "part rejected",[Parts booked into stores].[Form]![text23],"0")

You need to be explicit here.

=IIf([Parts booked into stores].[Form]![Passed]="Rejected" Or [Parts booked into stores].[Form]![Passed]="part rejected",[Parts booked into stores].[Form]![text23],"0")

Though maybe this would work too, but don't quote me on it.

=IIf([Parts booked into stores].[Form]![Passed] In ('Rejected', 'part rejected'),[Parts booked into stores].[Form]![text23],"0")

 

[steve111]

Hi
thank you all
this is working from post 5

=IIf([text28]=0,[qtyreqd]-[qtyrecd],([qtyreqd]-[qtyrecd]+[text28])*-1)

 

[gemma-the-husky]

despite this working, why *-1. why not simply

=IIf([text28]=0,[qtyreqd]-[qtyrecd],([qtyreqd]-[qtyrecd]-[text28]))

or as mile-o pointed out, the iif test is superfluous so simply

[qtyreqd]-[qtyrecd]-[text28]

you may also want to think about managing nulls.

 

[Brianwarnock]

despite this working, why *-1. why not simply

=IIf([text28]=0,[qtyreqd]-[qtyrecd],([qtyreqd]-[qtyrecd]-[text28]))

That does not give him what he wants
His example of the values 53,60,1 respectively
53-60-1=-8
He wants 6

Brian

 

[steve111]

hi
I am wrong it is not working
=IIf([text28]=0,[qtyreqd]-[qtyrecd],([qtyreqd]-[qtyrecd]+[text28])*-1)

qtyreqd =543
qtyrecd=1000
balance - 459

should be positive

steve

 

[steve111]

note
if I put a number in rejected{text28} then the balance goes to positive which is correct

 

[steve111]

I have attached a copy of the stores form , you will see qty reqd on the top form
and qty recd and qty rejected on the second form

 

[Mile-O]

qtyreqd 53
qtyrecd 53
text28 1 ( rejected qty)

should give me an answer of -1
but I get 1
if I increase the qtyrecd to 60 I get -6 yet I want 6

if I decrease the qtyrecd to 10 I get 44

What am I not seeing in your maths?


Example 1:

Requested = 53, Received = 53, Rejected = 1

[Requested] - [Received] + [Rejected]

Expected result = -1

Therefore, obviously

[Requested] - ([Received] + [Rejected])
53 - (53 + 1)
53 - 54
= -1​

Example 2:

"if I increase the qtyrecd to 60 I get -6 yet I want 6"

Increasing Received to 60 (NB: no other change)

Therefore:

Requested = 53, Received = 60, Rejected = 1

[Requested] - [Received] + [Rejected]

53 - 60 + 1
= -6

However, in Example 1, we added brackets to get the right result.

Adding brackets here would give us:

[Requested] - ([Received] + [Rejected])
53 - (60 + 1)
53 - 61
= -8​

Example 3:

"if I decrease the qtyrecd to 10 I get 44"

So, we now have:

Requested = 53, Received = 10, Rejected = 1

[Requested] - [Received] + [Rejected]

53 - 10 + 1
= 44

However, again the bracket logic:

[Requested] - ([Received] + [Rejected])
53 - (10 + 1)
= 53 - 11
= 42​

 

[Brianwarnock]

hi
I am wrong it is not working
=IIf([text28]=0,[qtyreqd]-[qtyrecd],([qtyreqd]-[qtyrecd]+[text28])*-1)

qtyreqd =543
qtyrecd=1000
balance - 459

should be positive

steve

And it would be if the formula said
qtyrecd-qtyreqd as I pondered in post 3

What answers do you want , assuming text28 is 0, if

Qtyreqd = 540 and qtyrecd = 500
Qtyreqd = 500 and qtyrecd = 540

Brian

 

[steve111]

hi

qtyreqd=540 qty recd = 500 I want -40
qtyreqd =500 qty recd =540 I want 40

this is with text28 empty

if text28 has 2 in it
I want -42
or
I want 38

sorry for the delay I just travelled china and I fell asleep

steve

 

[Brianwarnock]

Then my instinct in post 3 was correct all you need is

Qtyrecd-qtyreqd-qtyrejected

No iif is required

Brian