Format?

[drvajeep]

How would I get excel to figure the Hardware required for the number of treads (4 per tread) in the hardware box, with hardware listed, 3/8" bolts, nuts & washers. I need this box to reamin N/A if no hardware is required.

TREAD SHEET
GRATING TYPE:	1 1/4" x 3/16" PLAIN
NOSING TYPE:	CHECKER PLATE
FINISH:		GALVANIZED
HARDWARE:	N.A.
NO. OF	TREAD SIZE	"A" DIM.	HOLE	TRD. WT.	REFERENCE DRAWING
TREADS			DIAMETER
100	1'-0 1/8" X 3'-0"			31
10	10 15/16" X 2'-6"			28
110	PCS. TOTAL		TOTAL WT. :	3380

<colgroup><col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" width="80"> <col style="width: 135pt; mso-width-source: userset; mso-width-alt: 6582;" width="180"> <col style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;" width="51"> <col style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;" width="72"> <col style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;" width="60"> <col style="width: 139pt; mso-width-source: userset; mso-width-alt: 6765;" width="185"> <tbody>
</tbody>

 

[TKB]

Hello,

If I am not mistaken, at the bottom to the left of "PCS. TOTAL" is the total number of treads. If this is true, then in the cell next to Hardware you should try this:
=IF(Total PCS. > 0, Total PCS. *4, "N.A.")

Place that formula in the cell you currently show as "N.A."

Replace "Total PCS." in the formula with the cell reference you currently show 110 in. (eg. A1, C17, B34).

Hope thats what your looking for.

Trey

 

[drvajeep]

Thanks TKB that worked, it displayed the number in the hardware box, is there a way for me to add text before or after the number I would like it to read 1 1/4" x 3/8" Bolts, Nuts & Washers Req'd. 440, and display N.A. if no hardware is required.
Would it be easier to have the hardware note or N.A. in the cell, and have the total required in the cell to the right.

 

[TKB]

Hey drvajeep,

try using the & function. so the formula would look like this:

=IF(Total PCS. > 0, "Text Before" & (Total PCS. *4) & "Text After", "N.A.")

That should work (but i havent had time to check it out myself).​

 

[drvajeep]

Thank You very much, I will try it and see if it works.