If/and statement assistance, looking at mulitple cells as reference...

[brianv]

Alright guys… im struggling with this one, maybe I’ve been looking at it for too long…

Background….
Panel A DOES Not have a built in Comm Card, but…
Panel B has a built in Comm Card,

Both panels can have multiple Comm Cards, the number of Comm Cards is dependent on the number of Com Devices, each Comm card can accommodate 100 Comm devices

If I have 392 Comm devices, then panel B needs 4 Comm Cards and Panel A needs 3 (because Panel A does not include one built in).

Here’s the catch, there are 2 different Comm Cards and they work in pairs, Master & Slave.

So…
if I’m using Panel A, with 392 Comm devices, then I need 2 Masters & 2 Slaves
If I’m using Panel B, with 392 Comm devices, then I need 1 Master and 2 Slaves (because Panel B had 1 master card built in)
If I’m using Panel B, with 092 Comm devices, then I don’t need any additional Comm Cards (because Panel B had 1 master card built in)
If I’m using Panel B, with 192 Comm devices, then I need 1 Slave (because Panel B had 1 master card built in)

Workbook…
C8 is the Panel A selected
C9 is the Panel B selected

C68 is the qty of Master Comm Cards
C69 is the qty of Slave Comm Cards

D70 is the total number of Comm devices counted (arrived from a sum total elsewhere on the worksheet)
D72 is the total number of Comm Cards needed (essentially D70/100 rounded up)


So….
If C8=1 (Panel A is selected), and D70=392, then D72=4, then C68 should be 2 and C69 should be 2
If C9=1 (Panel B is selected), and D70=392, then D72=4, then C68 should be 1 and C69 should be 2 (because C68 is the Master Comm Card and 1 is already built into Panel B)

Im burned out on this one and could use help…..

Thank you....

 

[fhqwgads]

Panel A DOES Not have a built in Comm Card, but…
Panel B has a built in Comm Card,

Both panels can have multiple Comm Cards, the number of Comm Cards is dependent on the number of Com Devices, each Comm card can accommodate 100 Comm devices

If I have 392 Comm devices, then panel B needs 4 Comm Cards and Panel A needs 3 (because Panel A does not include one built in).

wont panel A need 4 and B need 3 in this case?

also

Here’s the catch, there are 2 different Comm Cards and they work in pairs, Master & Slave.

So…
if I’m using Panel A, with 392 Comm devices, then I need 2 Masters & 2 Slaves
If I’m using Panel B, with 392 Comm devices, then I need 1 Master and 2 Slaves (because Panel B had 1 master card built in)
If I’m using Panel B, with 092 Comm devices, then I don’t need any additional Comm Cards (because Panel B had 1 master card built in)
If I’m using Panel B, with 192 Comm devices, then I need 1 Slave (because Panel B had 1 master card built in)

so it looks like a master doesnt need a paired slave but a slave needs a paired master. so if you had 292 comm devices you'd need 2 masters and 1 slave but you couldnt have 2 slaves and 1 master, yes?

 

[brianv]

wont panel A need 4 and B need 3 in this case?

sorry, yes that is correct, i had reversed that order....

so it looks like a master doesnt need a paired slave but a slave needs a paired master. so if you had 292 comm devices you'd need 2 masters and 1 slave but you couldnt have 2 slaves and 1 master, yes?

Yes a master does not need a paired slave, but a slave wont work without a paired master (which is built-in on Panel B, so a slave can be paired with Panel B). The slave is dependent.

Well on Panel A, no.... On panel B, yes...
Panel A DOES NOT have a Master Comm Card built-in so for 292 devices, there will be 2 Master and 1 Slave Comm Card.

Panel B DOES have a Master Comm Card built-in, so for the same 292 device, the Comm Card built into panel B will have devices 1-99 on its comm card, 100-199 on a slave Comm Card and 200-292 on a 2nd Master Comm Card. 1 Master, 1 Salve.

Think of it this way...
Panel A - No Onboard Comm
Master Comm 0-99
Slave Comm 100-199
Master Comm 200-299
Slave Comm 300-399
etc....

Panel B - Onboard Comm (Master) 0-99
Slave Comm 100-199
Master Comm 200-299
Slave Comm 300-399

Thanks
BV

 

[fhqwgads]

maybe this?

Book1
	B	C	D
7		Panel
8	A	1
9	B	0
10
66
67
68	Master CC	2
69	Slave CC	2	Devices
70			392
71			CC
72			4
Sheet37

Book1
	B	C	D
7		Panel
8	A	0
9	B	1
10
66
67
68	Master CC	1
69	Slave CC	2	Devices
70			392
71			CC
72			4
Sheet37

Cell Formulas
Range	Formula
C68	=ROUNDUP(D72/2,0)-IF(C9=1,1,0)
C69	=ROUNDDOWN(D72/2,0)
D72	=ROUNDUP(D70+1,-2)/100

 

[brianv]

Ok, I think we are are the right track.... but let me clarify a few items then.....

I used 100 comm devices as generic number, to simplify the explanation, i didn't think it would alter the formula, but it does in this case because you were rounding up to the nearest 100... makes sense... i hadn't thought of that... but it was generally the direction i was headed i just was rounding to the nearest number.... (My original thought for D72 was D72=ROUNDUP(D70/140,0))

In actuality there are 140 comm devices on each comm card, probably should have written that specific, but i just didnt think it would have mattered... my error....

On another note:
How did you import those images in?

 

[brianv]

I think i got it...

C68=ROUNDUP(D72/2,0)-IF(C9=1,1,0)
C69=ROUNDDOWN(D72/2,0)
D72=ROUNDUP(D70,0)/140

 

[fhqwgads]

i use the mrexcel html maker to post bits of workbook sheets
https://www.mrexcel.com/forum/about-board/508133-attachments-post2545970.html#post2545970
i found it didnt work on my excel 2007 and not until i upgraded to office 365. not sure about office 2010