Excel Formula Help

Status
Not open for further replies.
L

Last Leg

Member
Location
Houston, TX
I have a panel schedule I set up on Excel. It adds up the phases, etc, and at the bottom I have misc. catagories - ltg, A/C, largest motor, etc w/ their respective code demands to give me my demand loads. I set up a column adjacent to the circuit numbers to which I want to assign letter (a,b,c...) corresponding to the catagories. How do I write a formula that will insert the corresonding watts into a column so that I can transfer that total automatically to the demand catagories at the bottom?
I want to say - If (my new column) B8=a then (my work column) M8 = (the watts entered on my panel schedule) E8. Can you help?
 
charlie b

charlie b

Moderator
Staff member
Location
Lockport, IL
Occupation
Retired Electrical Engineer
It can also be done with a lookup table. I have used Excel files that were built by someone else, and I had to make a minor adjustment to the lookup instruction. But I do not know how to create one from scratch. I hate to advise hitting the F1 button, but that is the only advice I have to give.
 
D

dkarst

Senior Member
Location
Minnesota
I'm not certain if this is what Charlie B. meant, but the Vlookup function searches for a value in your column (say "a"), then hops over a specified number of columns (where your data is), then returns that value. Will this work? Trying it out on a small array of test data will probably help as it is a little difficult to explain.
 
Dennis Alwon

Dennis Alwon

Moderator
Staff member
Location
Chapel Hill, NC
Occupation
Retired Electrical Contractor
Can you just enter the cell #. Thus if info in cell B8 is 1500 watts and you want that info in cell M8, you need to go to M8 and type +B8
 
Dennis Alwon

Dennis Alwon

Moderator
Staff member
Location
Chapel Hill, NC
Occupation
Retired Electrical Contractor
IF(logical_test,value_if_true,value_if_false)

Thus you could have =if(B8>5,B8,0)

This statement would look up cell B8 and if it was greater than 5 it would enter the vale of cell B8 otherwise if would enter 0 .
 
Smart $

Smart $

Esteemed Member
Location
Ohio
Last Leg said:
...

I want to say - If (my new column) B8=a then (my work column) M8 = (the watts entered on my panel schedule) E8. Can you help?
Click to expand...
In cell M8 enter the formula... =IF(B8="a",E8,"")

Suggest using Data Validation for column B and a table of load categories to right (which you can place outside your print area, and/or Hide if you want).

See attached Word doc. Open in Word, right click on Excel Object, expand Worksheet Object in the local menu and choose Open.

View attachment 4845
 
H

hardworkingstiff

Senior Member
Location
Wilmington, NC
rcwilson said:
Take a look at the SUMIF(..,...,..) command. With a little work you can make it do what you need.
Click to expand...

I stumbled across the SUMIF function one time and man it is SWEET.

I use a spread sheet to calculate the VD on feeders to marina power centers. The length of cable of each wire size need to be added up for quantities of each size and I use this formula to do it.

=SUMIF(K$7:K$145,M7,D$7: D$145)

Column K is the wire size of a particular feeder
Column D is the wire length of a particular feeder
Cell M7 has the size of the cable that I want to sum

Edit: I had to add a space in the formula (it shouldn't be there) just before D$145 so it did not show up as a grin.
 
Last edited:
Mike01

Mike01

Senior Member
Location
MidWest
command

command

Another good command is "istext", along with "sumif", and the different lookup tables using a combination of multiple forumlas you can create some pretty cool stuff, I also utilize macros to automate some processes.
 
Dennis Alwon

Dennis Alwon

Moderator
Staff member
Location
Chapel Hill, NC
Occupation
Retired Electrical Contractor
As Smart shows on his formula. The "" will return whatever is inside the quotes. Thus in an empty quote like show above the cell will be blank if that statement applies. You could use "NA" and NA would appear in the cell.
 
G

GrayHeadedMule

Member
Here is the formula I use when filling out our inspection sheet to calculate the type of inspection then how many inspections that particular inspection counts toward. I have this formula in a separate area than where the numbers actually go. I'll explain..for this example I will use the column I have posted the formula for...

Column H....going down column H, it is the plumbing column. If I put RI in that cell I want a number to be placed at the bottom of that column for the count of inspection.

Cell H3. H4. H5.........all the way down to H29 is the cell I put the inspection type. RI, TO, WL, SL and so on. In cell H30 is where the total of those inspection go.

389714213.jpg


389714212.jpg


Down under that area is where I have the formula for calculating the number based off of the two letter code I entered in to one of the H column cells.

Notice the area where I have the formulas is identical to the area of where the two letter codes go.

389714211.jpg


The formula for H3 is in cell H33. The formula for cell H4 is in H34.

When I enter RI in H3 the formula enters a number value in H30.

So what I am saying is that I may enter a two letter code (RI) but excell sees it a value and will give me a total at the bottom of the column.

Did I totally confuse you??

Here is the formula I have entered into H33.

=SUMPRODUCT(--(ISNUMBER(MATCH({"ri";"to";"to2";"f";"gas";"wh";"wl";"sl";"tap";"tub";"misc";"ri2";"f2"},H3,0))),{5;3;4;3;1;1;1;1;1;1;1;8;3})


Is this the formula you were inquiring about? I hope so....
 
Besoeker

Besoeker

Senior Member
Location
UK
Mike01 said:
Another good command is "istext", along with "sumif", and the different lookup tables using a combination of multiple forumlas you can create some pretty cool stuff, I also utilize macros to automate some processes.
Click to expand...
Yes, I also like to use macros sometimes written in Visual Basic. And I like to inject a bit of .....personality.
This is a little bit from a motor performance spreadsheet:

FLPF:
Range("P25").Select
ini = ActiveCell
PF = InputBox("Full Load Power Factor ?", , ini)
ActiveCell.FormulaR1C1 = PF
If PF > 1 Then Cretin

Sub Cretin()
Title = "Not a valid entry !"
Msg = "Some motor to have such an efficiency or power factor. Try again."
Style = vbCritical + vbDefaultButton2
R = MsgBox(Msg, Style, Title, Help, Ctxt)
End Sub

Just my British sense of humour...
 
Besoeker

Besoeker

Senior Member
Location
UK
GrayHeadedMule said:
Here is the formula I use when filling out our inspection sheet to calculate the type of inspection then how many inspections that particular inspection counts toward.
Click to expand...
A little of topic, but why didn't you just make column A wider rather than merging A, B, and C?
 
L

Last Leg

Member
Location
Houston, TX
Thanks guys,
Actually I got it from RC Wilson's first reponse - The SUMIF didn't work but it led me right - it ended up being - =IF(B18=M17,E18,0) B18 being my cell to enter my code letter, M17 is the cell name, for instance, 'a' above the column for that catagory, E18 is the corresponding watts for that circuit, '0' is the 'else' that is entered if the first equation isn't true. Anyway, now my working Panel Schedule does everything I need, almost.
 
Status
Not open for further replies.
Top