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

brianv

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

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
194
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
100
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
194
Office Version
  1. 2019
Platform
  1. Windows
maybe this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">Panel</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">B</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">66</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">67</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">68</td><td style="text-align: right;;">Master CC</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">69</td><td style="text-align: right;;">Slave CC</td><td style="text-align: right;;">2</td><td style=";">Devices</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">392</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">71</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">CC</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">72</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet37</p><br /><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">Panel</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">A</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">B</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">66</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">67</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">68</td><td style="text-align: right;;">Master CC</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">69</td><td style="text-align: right;;">Slave CC</td><td style="text-align: right;;">2</td><td style=";">Devices</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">392</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">71</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">CC</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">72</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet37</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C68</th><td style="text-align:left">=ROUNDUP(<font color="Blue">D72/2,0</font>)-IF(<font color="Blue">C9=1,1,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C69</th><td style="text-align:left">=ROUNDDOWN(<font color="Blue">D72/2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D72</th><td style="text-align:left">=ROUNDUP(<font color="Blue">D70+1,-2</font>)/100</td></tr></tbody></table></td></tr></table><br />
 

brianv

Board Regular
Joined
Dec 11, 2003
Messages
100

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
100
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,478
Messages
5,601,900
Members
414,482
Latest member
morkar

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