Posts by srands

Re: Calculate amperage based on variables wire length and max amps at sockets

HELLO Pangolin, that's excellent, thanks, exactly what I meant.

Are you familiar with the use of the RING/KIRCHOFF equation to calc which socket wire to use for a specific ring circuit? I've altered the figures as per the Kirchoff example in the hyperlink below. But it's not obvious to me which diameter skt wire is to be used. Can you comment on this revised workbook? SPREADSHEET

Just wondering did you have that file? Or work it out from the example http://www.voltimum.co.uk/file…00106819Kirchoff-Ring.pdf

Cheers

Stephan

Hi I need an EXCEL spreadsheet to calculate the est. approx AMPAGE of the RING CIRCUIT socket wire (240V ac) from just the following?:
~ SKT WIRE lengths from skt to skt, and last skt to fusebox
~ MAXIMUM AMPAGE of each skt

Eg:
Skt A to B = 10M's
Skt B to C = 5M's
Skt C to D = 3M's
Skt D to A = 7M's

Skt A ampage = 5A
Skt B ampage = 3A
Skt C ampage = 10A
Skt D ampage = 7A

I'm aware of Kirchoff (http://www.voltimum.co.uk/file…00106819Kirchoff-Ring.pdf See Pgs 2-4), but I can't be bothered to do that everytime.

In summary I want just a simple spreadsheet :cool: to enter:
the METRES OF SOCKET WIRE and
the AMPAGE MAXIMUM AT EACH SOCKET,
to calculate the RING CIRCUIT AMPAGE of the SKT WIRE.

I know there is software to do this, but surely this can be sorted into XL formulas easily.

Cheers

Stephan

Re: Play embedded sound file at intervals of X minutes

Hi Rut, after inserting your code suggestions in MODULE 1 after the SOUND MACRO. The sound file still only played twice.
Then I edited out the schedule variable, but this made no difference.

Perhaps my macro to play the sound wav, is at fault. My method was to insert a WAV as an object, then RECORD MACRO, dbl click the inserted/embedded sound object, listened to it played, then STOP THE MACRO. Then I used your code, but even with edit suggestions it still only played twice.

Could you upload/post an XL spreadsheet that works with a sound file?

Cheers

Stephan

Re: SOUND WAV, loop VISUAL BASIC macro every minute?

Thanks RUTS, but this code on Excel 2003 only plays twice, any ideas?

Cheers

Stephan

Hi, I have an XL spreadsheet with a sound WAV as an EMBEDDED OBJECT, saved as a Macro.

I have another Macro, to play the WAV after a period of time.

Sub MyMacro()
Application.OnTime Now + TimeValue("00:01:00"), "mysoundmacro" 
End Sub

However at present the sound file, just plays, twice. I want it to play every minute.

HOW CAN I LOOP THIS vb (Visual Basic) TO REPEAT THE CYCLE?

SEE SAMPLE FILE

Also of interest is a FORMULA or VISUAL BASIC for the MACRO WAV to play when CELL REF equals or less or greater then a certain value.

I have posted this query with another forum, but to no avail.
http://www.mrexcel.com/forum/s…php?p=2627017#post2627017

Cheers

Stephan

Hi, I want to add COMMAND BUTTONS (Or similar) to the following spreadsheet, but I don't know any VB code!:furious:

In column H I want COMMAND BUTTONS, when pressed I want the count in the next row to start at the NEXT CATEGORY. I am unfamiliar with the parameters of VB code, but in my spreadsheet it works detailed in "DESCRIPTION OF SPREADSHEET" below.

Example of what I want COMMAND BUTTONS to do, in H9, when command button pressed, row 10 count, starts count in next category
F10: A
G10: RANGE GROUP ii)

Perhaps control toolbox buttons (Or similar) is straight forward, or perhaps/maybe such COMMAND BUTTONS would need to calc the remainder of the current category:
Current Category Max, Column K - Current Category Running Total, Column M = REMAINDER
Then add remainder to current category in COLUMN M, then count starts at next category.

DESCRIPTION OF SPREADSHEET:
The formula fills (As much possible, without going over the max), the CATEGORIES only in order of RANGE GROUPS (5 ranges, each with 4 different categories):
"A", then "B", then "C", then "D", then if possible count starts again at "A", etc.

TIER RANGES example:

CATEGORY MIN MAX
A 0 100
B 0 300
C 0 500
D 0 800

DATA TABLE EG:
ROW 2, MISC ITEM, £50 X1 = £50, Running absolute total = £50, CATEGORY = A (Range A total so far = £50)

ROW 3, MISC ITEM, £30 X1 = £30, Running absolute total = £80, CATEGORY = A (Range A total so far = £80)

ROW 4, MISC ITEM, £30 X1 = £30, Running absolute total = £110, CATEGORY = B (Range B total so far = £30)

ROW 5, MISC ITEM, £50 X4 = £200, Running absolute total = £310, CATEGORY = B (Range B total so far = £230)

ROW 6, MISC ITEM, £80 X1 = £80, Running absolute total = £390, CATEGORY = C (Range c total so far = £80)

SEE XL SPREADSHEET EXAMPLE

Cheers

Stephan

SEE XL SPREADSHEET EXAMPLE

Hi, this of interest to the ELECTRICIANS amongst us (Or DIYers who fit their own fusebox consumer units) here are my two XL fusebox rcd: MCB & skt wire autocalc's. ::D

FUSEBOX CONSUMER UNIT with 1 RCD: MCB and SKT wire AUTOCALC
Stephan Rands' Website

FUSEBOX CONSUMER UNIT with 2+ RCDS: MCB and SKT wire AUTOCALC
Stephan Rands' Website

Edit to suit. Both have examples. The file sizes are a bit big at 0.8mb's each, so recommend, SAVE file, then OPEN.

Simply edit fusebox mcb amps/watts (R/H of spreadsheet, columns N and O), then add in list your lighting, skts/appliances (L/H of spreadsheet, columns A to F).

I like these, give me your comments if you like these XL spreadsheets. :cool: Or post your edits!

Functional note:
If MCB max NOT exceeded, but want to start next MCB, then calculate DIFFERENCE between the MAX WATTs of that MCB, and that MCB's running total, then enter misc line for that WATTs amount.
EG: 6 "a" running total = 1300. 6 "a" Watts Max = 1440 . 1440 - 1300 = 140 Watt's difference.
HENCE: misc line, 140 Watts (See included examples, very easy to follow)

You like that? Let me know if you think that is great.

Cheers

Stephan Rands


My list of my most excellent spreadsheets so far!

MY VERY COMPREHENSIVE ELECTRICITY WATER GAS ENERGY UTILITY METER COST CALCULATOR, 1ST TAB, SCROLL DOWN

EXAMPLE OF SIMPLE ELEC/WATER/GAS CALCULATOR ON MONEY TIER SPREADSHEET 2ND/3RD/4TH TAB

FUSEBOX CONSUMER UNIT with 1 RCD: MCB and SKT wire AUTOCALC

FUSEBOX CONSUMER UNIT with 2+ RCDS: MCB and SKT wire AUTOCALC

also posted with ELEC FORUM http://www.electriciansforums.co.uk/electrical-forum-general-electrical-forum/29958-excel-fusebox-rcd-mcb-skt-wire-autocalc-utility-nrg-megacalc.html#post237168

Re: IF formula with MIN value, as well as MAX, then TRUE, or FALSE (Or similar)

OK alot of further revisions to the above, here are my most current version TIER spreadsheets:

TIER SPREADSHEET: CATEGORY TYPE
DATA LIST (L/H) with SUMMARY TABLE (R/H) for MANY ROWS: 1ST TAB most accurate version, 2 examples following, followed by previous versions (To show progression!)
http://www.srands.co.uk/4+Tier_CATEGORY_TYPE.xls

TIER SPREADSHEET: MONEY TYPE
SUMMARY TABLE of MIN/MAX/RATES for ONE ROW.
http://www.srands.co.uk/4+Tier_MONEY_TYPE.xls

Let me know if you think thumbs up for these cool spreadsheets!

Cheers

Stephan

www.srands.co.uk

EXAMPLE OF FUSEBOX MCB CALCULATOR for LIGHTING CIRCUTS ON CATEGORY TIER SPREADSHEET 2ND & 3RD TAB

EXAMPLE OF SIMPLE ELEC/WATER/GAS CALCULATOR ON MONEY TIER SPREADSHEET 2ND/3RD/4TH TAB

MY VERY COMPREHENSIVE ELECTRICITY WATER GAS ENERGY UTILITY METER COST CALCULATOR, 1ST TAB, SCROLL DOWN

Re: Calculate running total excluding previous values in range

OK alot of further revisions to the above, so here are my most current version TIER spreadsheets:

TIER SPREADSHEET: CATEGORY TYPE
DATA LIST (L/H) with SUMMARY TABLE (R/H) for MANY ROWS: 1ST TAB most accurate version, 2 examples following, followed by previous versions (To show progression!)
http://www.srands.co.uk/4+Tier_CATEGORY_TYPE.xls

TIER SPREADSHEET: MONEY TYPE
SUMMARY TABLE of MIN/MAX/RATES for ONE ROW.
http://www.srands.co.uk/4+Tier_MONEY_TYPE.xls

Let me know if you think thumbs up for these cool spreadsheets!

Cheers

Stephan

www.srands.co.uk

EXAMPLE OF FUSEBOX MCB CALCULATOR for LIGHTING CIRCUTS ON CATEGORY TIER SPREADSHEET 2ND & 3RD TAB

EXAMPLE OF SIMPLE ELEC/WATER/GAS CALCULATOR ON MONEY TIER SPREADSHEET 2ND/3RD/4TH TAB

MY VERY COMPREHENSIVE ELECTRICITY WATER GAS ENERGY UTILITY METER COST CALCULATOR, 1ST TAB, SCROLL DOWN

Re: 4TIER+, MIN & MAX nested cell, if, lookup, HELP!

OK ALL SORTED NOW! So here are my most current version TIER spreadsheets:

TIER SPREADSHEET: CATEGORY TYPE
DATA LIST (L/H) with SUMMARY TABLE (R/H) for MANY ROWS: 1ST TAB most accurate version, 2 examples following, followed by previous versions (To show progression!)
http://www.srands.co.uk/4+Tier_CATEGORY_TYPE.xls

TIER SPREADSHEET: MONEY TYPE
SUMMARY TABLE of MIN/MAX/RATES for ONE ROW.
http://www.srands.co.uk/4+Tier_MONEY_TYPE.xls

Let me know if you think thumbs up for these cool spreadsheets!

Cheers

Stephan

www.srands.co.uk

EXAMPLE OF FUSEBOX MCB CALCULATOR for LIGHTING CIRCUTS ON CATEGORY TIER SPREADSHEET 2ND & 3RD TAB

EXAMPLE OF SIMPLE ELEC/WATER/GAS CALCULATOR ON MONEY TIER SPREADSHEET 2ND/3RD/4TH TAB

MY VERY COMPREHENSIVE ELECTRICITY WATER GAS ENERGY UTILITY METER COST CALCULATOR, 1ST TAB, SCROLL DOWN

Re: IF formula with MIN value, as well as MAX, then TRUE, or FALSE (Or similar)

RESOLVED, SEE COMPLETED EXAMPLES BELOW, formulas: vlookup & if

4 tier, 5 ranges, CATEGORY A to D http://www.srands.co.uk/4+TierRange_Datalist_many_rows.xls
or
4tier, 5 ranges, FUSEBOX circuits 6A, 16A, 32A, 40A http://www.srands.co.uk/FUSEBOX_TOTAL.xls

Thanks to Alphafrog's formula. http://www.mrexcel.com/forum/s…php?p=2607907#post2607907

Cheers

Stephan


www.srands.co.uk

Re: Calculate running total excluding previous values in range

RESOLVED, SEE COMPLETED EXAMPLES BELOW, formulas: vlookup & if

4 tier, 5 ranges, CATEGORY A to D http://www.srands.co.uk/4+TierRange_Datalist_many_rows.xls
or
4tier, 5 ranges, FUSEBOX circuits 6A, 16A, 32A, 40A http://www.srands.co.uk/FUSEBOX_TOTAL.xls

Thanks to Alphafrog's formula. http://www.mrexcel.com/forum/s…php?p=2607907#post2607907

Cheers

Stephan


www.srands.co.uk

Re: Calculate running total excluding previous values in range

You might NOT see, HOW what I want is any different to other common examples of "TIER RANGES".

Well the difference is commonly available examples work from "A" single total.

INSTEAD the type of TIER RANGE I need is ROWS of "MANY" TOTALS that are sorted into TIER RANGE CATEGORIES, then once a CATEGORY is filled, the cumulative count excludes the previously counted CATEGORIES.

see spreadsheet

Here is a more simple example:

The formula needs to fill (As much possible, without going over the max), the CATEGORIES only in order of RANGE GROUPS (5 ranges, each with 4 different categories):
"A", then "B", then "C", then "D", then if possible count starts again at "A", etc.

(Note: How to do this in a formula(s) from either a:
~ RUNNING TOTAL or ~ CUMULATIVE TOTAL. To exclude value of already counted CATEGORIES
LOOKUP with min & max? OR an IF with nested cells to subtract previously counted ranges from RUNNING TOTAL?).
Also CATEGORY max values maybe the same, how to include these, without calc going to next BIGGEST category.
In the DATA RANGE the total (variable) individual value will decide the category of that row, from the following RANGES, then next cumulative unless greater then the max of that category.

TIER RANGES:

CATEGORY MIN MAX
A 0 100
B 0 300
C 0 500
D 0 800


DATA TABLE EG:
ROW 2, MISC ITEM, £50 X1 = £50, Running absolute total = £50, CATEGORY = A (Range A total so far = £50)

ROW 3, MISC ITEM, £30 X1 = £30, Running absolute total = £80, CATEGORY = A (Range A total so far = £80)

ROW 4, MISC ITEM, £30 X1 = £30, Running absolute total = £110, CATEGORY = B (Range B total so far = £30)

ROW 5, MISC ITEM, £50 X4 = £200, Running absolute total = £310, CATEGORY = B (Range B total so far = £230)

ROW 6, MISC ITEM, £80 X1 = £80, Running absolute total = £390, CATEGORY = c (Range c total so far = £80)

Formula to calculate CATEGORY would be great.

Cheers

Stephan

see spreadsheet

IN OTHER WORDS:
I just need a formula to FILL the ranges (5 ranges each with 4 different categories) only in ORDER as per RANGE/CATEGORY min & max table, so filling in previous ranges is NOT possible, only current, if ITEM too large then next CATEGORY/RANGE that it fits in, etc.

RANGE MINs & MAXs: MIN =K2:K22 MAX L2:L22: see spreadsheet.

NOTE: I need to change these range min and max values as & when.

What would you want to happen if the first item cost £400 and the second item £50?
Item 1 & 2, C range i (Running Total £450),

If items 3, 4 and 5 cost £30, £50 and £100, where should these go?
Item 3, C range i (Running Total £480)
Item 4, D range i (Running Total £150)

HERE IS A DRAFT OF MY SPREADSHEET IN PROGRESS, COLUMN's F, G & O2:O21 are the formula's that need REPLACING/EDITING

Cheers for any help, much appreciated, as this dilemma has been troubling me for sometime! I have also posted this question with other XL forums, without any success so far, and even after searching through all the XL4kids websites, I haven’t found a formula that would edit to work with my tier category/range!

OTHER Q’s POSTED:
http://www.mrexcel.com/forum/showthread.php?p=2604760#post2604760
http://www.excelforum.com/excel-general/763564-if-formula-exclude-previous-counted-ranges-4-tier.html#post2468244
http://www.ozgrid.com/forum/showthread.php?t=150469
http://www.ozgrid.com/forum/showthread.php?t=150548

I want a spreadsheet to make CUMULATIVE calculations from TIER ranges, per many rows of data.

The formula needs to fill (As much possible, without going over the max), the CATEGORIES only in order of RANGE GROUPS (5 ranges, each with 4 different categories):
"A", then "B", then "C", then "D", then if possible count starts again at "A", etc.

(Note: How to do this in a formula(s) from either a:
~ RUNNING TOTAL or ~ CUMULATIVE TOTAL. To exclude value of already counted CATEGORIES
LOOKUP with min & max? OR an IF with nested cells to subtract previously counted ranges from RUNNING TOTAL?).


Also CATEGORY max values maybe the same, how to include these, without calc going to next BIGGEST category.

In the DATA RANGE the total (variable) individual value will decide the category of that row, from the following RANGES, then next cumulative unless greater then the max of that category.

TIER RANGES:

CATEGORY MIN MAX
A 0 100
B 0 300
C 0 500
D 0 800

DATA TABLE EG:
ROW 2, MISC ITEM, £50 X1 = £50, Running absolute total = £50, CATEGORY = A (Range A total so far = £50)

ROW 3, MISC ITEM, £30 X1 = £30, Running absolute total = £80, CATEGORY = A (Range A total so far = £80)

ROW 4, MISC ITEM, £30 X1 = £30, Running absolute total = £110, CATEGORY = B (Range B total so far = £30)

ROW 5, MISC ITEM, £50 X4 = £200, Running absolute total = £310, CATEGORY = B (Range B total so far = £230)

ROW 6, MISC ITEM, £80 X1 = £80, Running absolute total = £390, CATEGORY = c (Range c total so far = £80)

Formula to calculate CATEGORY would be great.

Cheers

Stephan

see spreadsheet

IN OTHER WORDS:
I just need a formula to FILL the ranges (5 ranges each with 4 different categories) only in ORDER as per RANGE/CATEGORY min & max table, so filling in previous ranges is NOT possible, only current, if ITEM too large then next CATEGORY/RANGE that it fits in, etc.

RANGE MINs & MAXs: MIN =K2:K22 MAX L2:L22: see spreadsheet.

NOTE: I need to change these range min and max values as & when.

What would you want to happen if the first item cost £400 and the second item £50?
Item 1 & 2, C range i (Running Total £450),

If items 3, 4 and 5 cost £30, £50 and £100, where should these go?
Item 3, C range i (Running Total £480)
Item 4, D range i (Running Total £150)

HERE IS A DRAFT OF MY SPREADSHEET IN PROGRESS, COLUMN's F, G & O2:O21 are the formula's that need REPLACING/EDITING

Cheers for any help, much appreciated, as this dilemma has been troubling me for sometime! I have also posted this question with other XL forums, without any success so far, and even after searching through all the XL4kids websites, I haven’t found a formula that would edit to work with my tier category/range!

OTHER Q’s POSTED:
http://www.mrexcel.com/forum/showthread.php?p=2604760#post2604760
http://www.excelforum.com/excel-general/763564-if-formula-exclude-previous-counted-ranges-4-tier.html#post2468244
http://www.ozgrid.com/forum/showthread.php?t=150469
http://www.ozgrid.com/forum/showthread.php?t=150548


see spreadsheet

Hi I need a IF formula with MIN value, as well as MAX, then TRUE, or FALSE (Or similar).

Hence in the example below, for each nested parted the formula checks another cell for the MIN value.

=IF(E2<=$L$2,$M$2,IF(E2<=$L$3,$M$3,IF(E2<=$L$4,$M$4,IF(E2<=$L$5,$M$5,"NEXT"))))

Eg in the 1st nested part SOIMETHING LIKE =IF(E2<=$L$2, E2 > $K$2,

Can this be done with IF FORMULA in a simple way? Or possible with another formula, in a simple way?

I want the formula to count from zero (MIN) to the (MAX) value for each nested part.

Cheers

Stephan

Re: Calculate running total excluding previous values in range

NEW SPREADSHEET EXAMPLE. OLD spreadsheet example.

OK I know the previous description is lengthy, so here is a shorter more concise description:

RANGES:
0w's to 1440W's, 6A MCB (1st range)
0w's to 3850W's, 16A MCB (2nd range)
0w's to 7680W's, 32A MCB (3rd range)
0w's to 9600W's, 40A MCB (4th range)

An example of RAW DATA:
light 1 = 1000w, running total of current (1st) range 1000w, MCB 6A
light 2 = 300w, running total of current (1st) range 1300w, MCB 6A
light 3 = 200w, running total of current (2nd) range 200w, MCB 16A
light 4 = 3000w, running total of current (2nd) range 3200w, MCB 16A
light 5 = 600w, running total of current (2nd) range 3800w, MCB 16A
light 6 = 3000w, running total of current (3rd) range 3000w, MCB 32A
light 7 = 2000w, running total of current (3rd) range 5000w, MCB 32A
light 8 = 2500w, running total of current (3rd) range 7500w, MCB 32A
light 9 = 150w, running total of current (3rd) range 7650w, MCB 32A
light 10 = 4000w, running total of current (4th) range 4000w, MCB 40A
light 11 = 4100w, running total of current (4th) range 8100w, MCB 40A

Or in words, PURPOSE DESCRIPTION OF FORMULA to be created for use IN COLUMN I (1st tab):
From a RUNNING WATTS TOTAL, a RANGE value is reached, and a product is set (MCB, ELEC RELAY, 1, 2, 3 OR 4), if a higher value, THEN the NEXT higher RANGE (MCB, ELEC RELAY 2, 3, OR 4), and so on, etc.

=IF(H2<=RANGES!$C$2,RANGES!$D$2,IF(H2<=RANGES!$C$3,RANGES!$D$3,IF(H2<=RANGES!$C$4,RANGES!$D$4,IF(H2<=RANGES!$C$5,RANGES!$D$5,IF(H2<=RANGES!$C$6,RANGES!$D$6,IF(H2<=RANGES!$C$7,RANGES!$D$7,IF(H2<=RANGES!$C$8,RANGES!$D$8,IF(H2<=RANGES!$C$9,RANGES!$D$9,"NEXT"))))))))

HOWEVER ABOVE FORMULA NEEDS EDITING, SO THAT PREVIOUS TOTAL WATT'S OF ALL PREVIOUS RANGES (all previous MCB'S) MUST BE EXCLUDED.

COLUMN I (elec relay MCB), cumulatively counts from COLUMN H (RUNNING WATTS TOTAL). However when COLUMN I changes when it reaches the maximum of a RANGE (1 of 4, that typically get larger through each range), the RUNNING WATTS TOTAL either needs:
~ RESETTING to ZERO for the NEW range count
or
~ ALL PREVIOUS WATTS counted in COLUMN F, must be subtracted from the current RUNNING WATTS TOTAL,
for the cumulative count to start again through the predetermined RANGES
(See 2nd tab of NEW SPREADSHEET EXAMPLE).

Surely some of you XL boffins, have formula solutions that would be suitable!

If you look at the spreadsheet you will clearly see what I'm trying to achieve.

Cheers

Stephan

NEW SPREADSHEET EXAMPLE. OLD spreadsheet example.

Re: Calculate running total excluding previous values in range

Hi AAE (Glad you liked my other spreadsheet enough to put it in "Hey that is cool!" spreadsheets) & Jack_May.

I'll start by stating the purpose of the spreadsheet, which is followed by examples. NEW SPREADSHEET EXAMPLE. OLD spreadsheet example.

RANGES defines the NUMBER of grouped RANGES, their individual MINIMUM (2nd tab, Column B) and MAXIMUM (2nd tab, Column C).

RAW DATA is a table list of CEILING LIGHTS, each row is a new light to be fitted, to a circuit range, these rows are added together in a RUNNING TOTAL.
(These circuits have relays they come in different sizes, their capacity is rated in Watts & Amps, these relays are called MCB’s),

THE NEAREST SIZE RANGE IS SET PER CIRCUIT(S) UNTIL THE RANGE MAX WILL BE EXCEEDED, THEN THE NEXT HIGHEST RANGE (MCB) IS SET, THEN THE RUNNING TOTAL IS TO EXCLUDE THE WATT’S OF ALL PREVIOUSLY SET RANGES.

CURRENTLY, the SPREADSHEET DOES NOT EXCLUDE PREVIOUS RANGES SET, this is an error, I need a solution for. Below under headings RANGES & DATA is how COLUMN I should be calculated TO EXCLUDE PREVIOUSLY COUNTED RANGES.

NOTE: See bottom of this message for below example in table format.

EG:
RANGES:
Range 1: 0W to 1440W, PRODUCT = MCB 6A.

Range 2: 0W to 3840W, PRODUCT = MCB 16A.

Range 3: 0W to 7680W, PRODUCT = MCB 32A.

Range 4: 0W to 9600W, PRODUCT = MCB 40A.

DATA:
Row 2 = 300w, running total 300w. SUBTRACT PREVIOUS RANGES WATTS VALUE = 0w. (MCB 6 totalling 300w)

Row 3 = 800w, running total 1100W. SUBTRACT PREVIOUS RANGES WATTS VALUE = 0w. (MCB 6A totalling 300w+800w=1100w).

Row 4 = 1200w, running total 2300w. SUBTRACT PREVIOUS RANGES WATTS VALUE = 1100W. (max of MCB6A > 1440w hence new range, MCB 16A totalling 2300w-1100w=1200w).

Row 5 = 1200w, running total 3500w. SUBTRACT PREVIOUS RANGES WATTS VALUE = 1100W. (MCB 16A totalling 3500w-1100w=2400w).

Row 6 = 1200w, running total 4700w. SUBTRACT PREVIOUS RANGES WATTS VALUE = 1100W. (MCB 16A totalling 4700w-1100w=3600w).

Row 7 = 1200w, running total 5900w. SUBTRACT PREVIOUS RANGES WATTS VALUE = 1100W+3600W. (max of MCB16A > 3840w hence new range, MCB 32A totalling 5900w-1100w-3600w=1200w).

Etc.

The RANGE maximum sizes, typically in size order:
~ 0 w's to 1440 Watt's (Note: 1440 W's / 240Volts = 6Amp MCB. Other Note: The 1440 is NOT to be confused with minutes, this is a coincidence)
~ 0 w's to 3840 Watt's (Note: 3840 W's / 240V = 16A MCB)
~ 0 w's to 7680 Watt's (Note: 7680 W's / 240v = 32A MCB)
~ 0 w's to 9600 Watt's (Note: 9600 W's / 240v = 40A MCB)

RAW DATA: Table list in COLUMN A to COLUMN E (List of ceiling lights, with different wattage bulbs, and differing quantity of bulbs per light)

FORMULAS: COLUMN F to COLUMN J.
Column F: Total Watts (ok)
Column G: Ampage of lights (ok)
Column H: Running Watts total (NOT OK, needs to be per range)
Column I: MCB (NOT OK, needs editing, to exclude previous ranges)

RANGES: See NEW spreadsheet, 2nd worksheet “RANGES”
(Or OLD spreadsheet, 1st worksheet COLUMN L to COLUMN T, row 1 to row 22)

FOR EXAMPLE:
IN the spreadsheet ROW 2, COLUMN H: The running total is 300W’s (100w’s x 3),
Then Row2, Column I, calculates from 1st range: Running total < less then 1st range max, hence = MCB 6A.

TABLE of what formula in COLUMN I should calculate:
[ATTACH=CONFIG]36195[/ATTACH]
HENCE in the picture link above, COLUMN I (MCB), the FORMULA SHOULD CALCULATE to SET the 1ST RANGE (OR NEXT RANGE, ETC) WITHOUT EXCEEDING MAXIMUM, and EXCLUDE ANY PREVIOUS RANGES ALREADY FILLED):
In the example
~ 1st MCB 1100W fitted, into the maximum capacity of 1440W.
~ 2nd MCB 3600W fitted, into the maximum capacity of 3840W (Excluding all previous ranges: In this instance:1st range of 1100W)
~ 3nd MCB 7200W fitted, into the maximum capacity of 7680W (Excluding all previous ranges: 1st range of 1100W & excluding 2nd range

COMPARISON/EQUIVALENT!:
I know all of the above might sound confusing/complicated, but it isn’t I’ll make a comparison so you can understand how watts of lights fit into a MCB circuit maximum:

imagine the ROWS are measures of drinks and

imagine the RANGES are different glass sizes,

From the running total (liquid) a glass (range) is started to be filled, or
IF the next measure is greater then the remaining space within the glass, then onto the next size glass (Or next etc).
THEN the running total for the next glass does NOT count previous measures already counted into glasses.

Cheers

Stephan

NEW SPREADSHEET EXAMPLE. OLD spreadsheet example.

Hi, I'm working on a spreadsheet http://www.srands.co.uk/FUSEBOX_TOTAL.xls

With an IF formula, that works through multiple number ranges, from a calculated total.

=IF(H4<=$N$2,$O$2,IF(H4<=$N$3,$O$3,IF(H4<=$N$4,$O$4,IF(H4<=$N$5,$O$5,IF(H4<=$N$6,$O$6,IF(H4<=$N$7,$O$7,IF(H4<=$N$8,$O$8,IF(H4<=$N$9,$O$9,"NEXT"))))))))

From a RUNNING TOTAL it reaches a RANGE value and assigns a PRODUCT, if a higher value, THEN the NEXT higher range value product, and so on, etc.

HOWEVER my calc doesn't take into account that the previous running total from all previous RANGE(s) MUST NOT be calculated as well, I'm sure there is a simple FORMULA solution, but I don't what it is!

For example In I4, the formula should exclude (H2:H3) of previous RANGE (I2:I3)
THEN
For example in I6, the formula should exclude (H2:H5) the previous RANGES (I2:I5)
AND SO ON

The product type heading in row I is a product type (Title:MCB), the actual data will be different every time, hence product RANGE criteria will be reached in different rows, depending on the total reached (H47), that is then sorted/calculated into the product ranges. The RANGES min & max I have defined in ROWs, M2:M5 & N2:N5.

This is driving me crazy, I'm sure this will be really simple for EXCEL experts amongst this forum.

OK thanks.

Stephan

spreadsheet with IF formula to EDIT

http://www.srands.co.uk/FUSEBOX_TOTAL.xls

[ATTACH=CONFIG]36189[/ATTACH]