What If??

[Guest]

In a monthly report I have a requirement to track a certain type of day (
AE10 ) that is dependant upon two other day group conditions ( L1010, and
AB10 ). The answer cell ( AE10 ) should indicate either a 0 or 1.

L1010 ( ie. =SUM 3.8 hrs )
AB10 ( Control Check box, Linked to AI10 )
AH10 ( =IFAI10.1.0 )
AE10 ( Should be 0 or 1, depending upon L1010, and AB10 )

If L1010 has accumulated hours, and AB10 is Contrtol Box Checked (1),
then AE10 =1.
If L1010 has no accumulated hours, and AB10 is Control Box Checked (1),
then AE10=0.
If L1010 has no accumulated hours, and AB10 is not Control Box Checked
(0), then AE10=0.
If L1010 has no accumulated hours, and AB10 is Not Control Box Checked
(0), then AE10=0.

Help is appreciated! Thanks

 

[Max]

Assuming you're using the Check box from the Forms Toolbar, here's some
options to play with:

As it stands, with your set-up as described,

Put in AE10, either:
=IF(AND(SUM(L1010)<>0,AH10=1),1,0)
or
=IF((SUM(L1010)<>0)*(AH10=1),1,0)

But I think we can dispense with the formula in AH10,
and read the checkbox's link cell AI10 directly

In which case, we could try instead in AE10, either:
=IF(AND(SUM(L1010)<>0,AI10),1,0)
or
=IF((SUM(L1010)<>0)*AI10,1,0)

(the formula in AH10 can be deleted)

---

 

[Guest]

Thanks ... Your info was most helpful. I had to change it slightly as I had
given all of the correct parameters. It works!

.... Kane

 

[Max]

Glad it worked, Kane !
You're welcome, and thanks for the feedback ..

 

[Guest]

MAx.. Not sure if I can use this route again, but here goes!
I now have a real problem that expands upon this scenario. I changed the
formula to read < =If(AND(SUM(L1010)=0,AI10),1,0) > , as I was incorrect in
stating condition 2. Condition 2 should have been < L1010 has no
accumulated hours( 0.0), and AB10 is checked (1), then AE10=1>.

To start over might be better. We want to record chargeable "Standby" time
dependant upon the# of truck types ( dependant upon weight ) driven ( max of
three types ). A selection is made ( one of 7 combinations ) using Control
Box,"CheckBox" (1 or 0) and supporting cell (format control, checkbox)
indicating the time ( 0.0 hours).
If there is no actual time (hours) entered , & or hours driven distribution,
then Standard "StandBy" hours( 1=7.8 , 2=3.9 each, 3=2.6 hours) are entered
in the max of three designated truck type columns..
I changed your formula to< =IF(AND(SUM(D10:K10:L1010)=0,Al10),1,0) > and
it worked fine. Then, the rules changed and here are the real scenario's!
**** 4 Conditions
1.> If Start and Stop hours are entered in B10 & C10, with results in I10;
If < D10:K10:L1010 > HAS actual hours accumulated (5.0); and <
AB10 > is Control box "Checked" (1), then < AQ10=1 >
2.> If < D10:K10:L1010 > has NO accumulated hours (0.0) entered; and <
AB10 > is control box checked (1); then < AQ10=1 >
3.>If D10:K10:L1010 > has accumulated hours (5.0), and < AB10 > is NOT
checked (0), then < AQ10=0 >.
4,> If < D10:K10:L1010 >has NO accumulated hours (0.), and < AB10 > is NOT
checked; then < AQ10=0 >

The time distribution determined by the number of truck types is based on
7.8 hours per day,and is selectable using a control check box.

If 1, then AM54,AM55, AM56 =7.8 hours
If 2, then AP54, AP55, AP56=7.8/2=3.9 hours
If 3, then AS54=7.8/3=2.6 hours

I hope that this makes some kind of sence!?

Should I re-post this ?

 

[Max]

Admit it's a bit confusing, Kane <g>. Anyway, here's one deep guess at
interpreting your set-up and what you're after

Put in AQ10:
=IF(AND(SUM(D10,K10,L1010)>=5,NOT(AI10)),0,
IF(AND(SUM(D10,K10,L1010)=0,NOT(AI10)),0,1))

Test out the output in AQ10 with all the scenarios
(Lightly tested here, it seems to function as it should)

Some clarifications:
I've presumed the checkbox's link cell is still AI10, and that you want to
monitor cells: D10, K10, and L1010. Following through from your orig.
post, I've read your description of: "L1010" to mean *all* the cells
between L10 to P10, inclusive. If this is not the case and you actually
mean: L10, P10 only, then just change the part:

SUM(D10,K10,L1010)

to

SUM(D10,K10,L10,P10)

in AQ10's formula

And I've also disregarded your line:

1.> If Start and Stop hours are entered in B10 & C10, with results in I10;

as cells B10, C10 and I10 don't seem to figure in the scenario details which
follow thereafter ..

---

 

[Guest]

Max... I have not tried this yet,but a question re. Condition1. The time I
indicated (5.0) could be any number of hours & minutes, depending upon the
input start (B10) and stop (C10) time. How would this affect the below AQ10
formula?

....Kane

 

[Max]

How would this affect the below AQ10 formula?

Yes, think there'll be impact on the criteria (eg: is time entered as time
format: 3:30, or as a number: 3.5, etc.)

Think it's much easier to take a look at what you really have over there.
Could you upload a "sanitized" small sample copy of your file via a free
filehost* and then post the *link* to it in response here (the link will be
generated when you "upload" in the filehost (follow the instructions there),
then just copy the link and paste it into your reply to this post)

*Some free filehosts that could be used:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

For cjoint.com (it's in French), just click "Browse" button,
navigate to your folder > select the sample file > Open, then click the
button centred in the page below (labelled "Creer le lien Cjoint") and it'll
generate the link. Then copy & paste the generated link as part and parcel
of your response here.

Kindly note that no attachments
should be posted *directly* to the newsgroup

Posting a link to your sample here (instead of going off-line) will enable
the discussions to be continued within the newsgroup, visible to and for the
benefit of all readers. And there could well be better insights coming from
others, too <g>.

 

[Guest]

Max... I would like to follow up on this within the next week. I have been
out of town and not had time to continue. ....

Kane

 

[Max]

No prob. Just upload your sample and post the link. Then we can take a look
at it.