Impossible IIf Expression

[Guest]

I have a report that needs to calculate the efficiency of the number of
products produced compared to the number expected, based on up time and down
time.

I have three cells for this expression, one for each shift, located in the
header of the report. I wil just show you my IIf for shift 1.

=IIf([shift]="1",[qtyproduced]/((Sum[qtyruntime]+[timedown])*[piecesperhour])))

My end result is usually blank. The problem I have is the shifts are
separated by department numbers and within the department numbers their are
several production lines. The table for the pieces per hour is different for
each line, so I don't think the formula know which number to choose from to
make it result.

Any thoughts on this would be great. Already have at least 40 hours into
this.

Thanks in advance.

 

[fredg]

I have a report that needs to calculate the efficiency of the number of
products produced compared to the number expected, based on up time and down
time.

I have three cells for this expression, one for each shift, located in the
header of the report. I wil just show you my IIf for shift 1.

=IIf([shift]="1",[qtyproduced]/((Sum[qtyruntime]+[timedown])*[piecesperhour])))

My end result is usually blank. The problem I have is the shifts are
separated by department numbers and within the department numbers their are
several production lines. The table for the pieces per hour is different for
each line, so I don't think the formula know which number to choose from to
make it result.

Any thoughts on this would be great. Already have at least 40 hours into
this.

Thanks in advance.

What do you mean by 'the shifts are separated by department numbers
and within the department numbers their are several production
lines.'?
If the value of [Shift] is something like "Shipping Shift 1" it can
NEVER = 1.

What does the possible data in [Shift] look like?
Are any of the fields used in the xpression Null?
Also, what do you wish to show if the value is NOT Shift1?

 

[Guest]

Here is the structure:

As an example, I have department numbers 2000, 3000, 4000, 5000, etc.
Department 2000, which consists of only one production line, has an
expectation of outputting 200 pieces of material per hour, but department
number 3000 has 3 lines and is expected to output 100 pieces for line 1, 200
for line 2 and 250 for line 3. Same goes for a few of the other departments.
The column that references [shift] only has three different entries: 1, 2,
and 3. No preceding alpha references, just numbers. This is a report that
was already created and I am just needing to modify it.

Other calculations in similarity on the report went like this:
=Sum(IIf([Shift]="1",[QtyProduced]-[QtyScrapped],0)), which produced the
output of total good product producted minus rejected product. I tried
putting Sum in front of my calculation, but got an aggregate function error.

My calculation works fine in the rest of the report where I do not have to
use the IIf([shift]="#") when I am after individual line percentages, but
unfortunately I need it separated out in the header section for the total
shift production and the expression cannot distinguish what number to use in
the calculation for the shift percentage because there are multiple
production lines using different expected pieces per hour.

Please let me know if I can provide more information and thank you for your
time.

 

[Guest]

Fred,

I neglected to include an answer to your last post. Yes there are null
values in the report. The design of my formula changes only by the shfit
numbers: [shift]="1" or "2" or "3". My goal is to take the quanity produced
for a specified shift and divide it by the calculation of total uptime and
down time and these two cells are multiplied by the pieces per hour that were
expected of that shift. On shift 1 for example, they produced plastic items
and line 1 is expected to produce 200 pieces in one hour for one department,
but some departments produce multiple lines of material and each line has a
different expected of them.

I hope I'm not too confusing for you, but this is what I'm looking at.

Shift: 1
Department: 2001
Total actual produced: 100
Quantity expected to be produced for an 8 hour period: 125 per hour
For Line one only

Shift: 2
Department 3000
Total acutal produced: 98
Quantity expected to be produced: 111 per hour
For line 2
Total actual produced: 101
Quantity expected produced: 122 per hour
This is for line 3, but within the same department number. The calculation
cannot determine which number to use to multiply by 111 or 122.


Thank you so much.
--
Johnny

I have a report that needs to calculate the efficiency of the number of
products produced compared to the number expected, based on up time and down
time.

I have three cells for this expression, one for each shift, located in the
header of the report. I wil just show you my IIf for shift 1.

=IIf([shift]="1",[qtyproduced]/((Sum[qtyruntime]+[timedown])*[piecesperhour])))

My end result is usually blank. The problem I have is the shifts are
separated by department numbers and within the department numbers their are
several production lines. The table for the pieces per hour is different for
each line, so I don't think the formula know which number to choose from to
make it result.

Any thoughts on this would be great. Already have at least 40 hours into
this.

Thanks in advance.

What do you mean by 'the shifts are separated by department numbers
and within the department numbers their are several production
lines.'?
If the value of [Shift] is something like "Shipping Shift 1" it can
NEVER = 1.

What does the possible data in [Shift] look like?
Are any of the fields used in the xpression Null?
Also, what do you wish to show if the value is NOT Shift1?