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

brianv

Board Regular
Joined
Dec 11, 2003
Messages
102
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....
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
215
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Dec 11, 2003
Messages
102
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

Board Regular
Joined
Jul 17, 2018
Messages
215
Office Version
  1. 2019
Platform
  1. Windows
maybe this?


Book1
BCD
7Panel
8A1
9B0
10
66
67
68Master CC2
69Slave CC2Devices
70392
71CC
724
Sheet37



Book1
BCD
7Panel
8A0
9B1
10
66
67
68Master CC1
69Slave CC2Devices
70392
71CC
724
Sheet37
Cell Formulas
RangeFormula
C68=ROUNDUP(D72/2,0)-IF(C9=1,1,0)
C69=ROUNDDOWN(D72/2,0)
D72=ROUNDUP(D70+1,-2)/100
 

brianv

Board Regular
Joined
Dec 11, 2003
Messages
102

ADVERTISEMENT

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

Board Regular
Joined
Dec 11, 2003
Messages
102
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,673
Messages
5,838,710
Members
430,566
Latest member
ChanchalSingh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top