Brand new Excel user

[Bob Barker]

Sorry for the basic excel questions, but I need some help with a spreadsheet. It has to do with money, so all the data is in dollars and cents.

There are 25 rows that list customers and what they spent that is seperated into four different columns. I can get totals and averages for the columns, but what I am having a problem with is getting the totals and averages for the sum totals of each row. Since the spreadsheet is filled in as customers come in, there will be rows that have no data.

When I sum up 4 cells in a row, I need the target cell to:

1. Be empty if there is no data in any of the 4 cells
2. Show $0.00 if the cells have $0.00 entered in all of them
3. Show the sum of the cells if any/all of them have a $$ amount

Once that is done, I know how to get a total of all the sums, but I am having a problem getting an average because the cells show $0.00 when there is no data and that is messing with my average calculation.

 

[Bob Barker]

and here is the totals and averages from that spreadsheet

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
26				Totals	$0.00	$0.00	$0.00	$0.00	Overall F&I	$0.00
27				Averages	N/A	N/A	N/A	N/A	Overall Average	N/A
28
29			NF	Totals	$0.00	$0.00	$0.00	$0.00	NF Total F&I	$0.00
30				Averages	N/A	N/A	N/A	N/A	NF Average F&I	N/A
31
32			UF	Totals	$0.00	$0.00	$0.00	$0.00	UF Total F&I	$0.00
33				Averages	N/A	N/A	N/A	N/A	UF Average F&I	N/A
34
35			NC	Totals	$0.00	$0.00	$0.00	N/A	NC Total F&I	$0.00
36				Averages	N/A	N/A	N/A	N/A	NC Average F&I	N/A
37
38			UC	Totals	$0.00	$0.00	$0.00	N/A	UC Total F&I	$0.00
39				Averages	N/A	N/A	N/A	N/A	UC Average F&I	N/A
Sheet1

Excel 2010

Cell Formulas
Range	Formula
E26	=SUM(E2:E25)
E27	=IF(ISERROR(AVERAGE(E2:E25)),"N/A",AVERAGE(E2:E25))
E29	=SUMIF(C2:C25,"NF",E2:E25)
E30	=IF(ISERROR(AVERAGEIF(C2:C25,"nf",E2:E25)),"N/A",AVERAGEIF(C2:C25,"nf",E2:E25))
E32	=SUMIF(C2:C25,"uf",E2:E25)
E33	=IF(ISERROR(AVERAGEIF(C2:C25,"uf",E2:E25)),"N/A",(AVERAGEIF(C2:C25,"uf",E2:E25)))
E35	=SUMIF(C2:C25,"nc",E2:E25)
E36	=IF(ISERROR(AVERAGEIF(C2:C25,"nc",E2:E25)),"N/A",(AVERAGEIF(C2:C25,"nc",E2:E25)))
E38	=SUMIF(C2:C25,"uc",E2:E25)
E39	=IF(ISERROR(AVERAGEIF(C2:C25,"uc",E2:E25)),"N/A",(AVERAGEIF(C2:C25,"uc",E2:E25)))
F26	=SUM(F2:F25)
F27	=IF(ISERROR(AVERAGE(F2:F25)),"N/A",AVERAGE(F2:F25))
F29	=SUMIF(C2:C25,"nf",F2:F25)
F30	=IF(ISERROR(AVERAGEIF(D2:D25,"nf",F2:F25)),"N/A",AVERAGEIF(D2:D25,"nf",F2:F25))
F32	=SUMIF(C2:C25,"uf",F2:F25)
F33	=IF(ISERROR(AVERAGEIF(D2:D25,"uf",F2:F25)),"N/A",(AVERAGEIF(D2:D25,"uf",F2:F25)))
F35	=SUMIF(C2:C25,"nc",F2:F25)
F36	=IF(ISERROR(AVERAGEIF(D2:D25,"nc",F2:F25)),"N/A",(AVERAGEIF(D2:D25,"nc",F2:F25)))
F38	=SUMIF(C2:C25,"uc",F2:F25)
F39	=IF(ISERROR(AVERAGEIF(D2:D25,"uc",F2:F25)),"N/A",(AVERAGEIF(D2:D25,"uc",F2:F25)))
G26	=SUM(G2:G25)
G27	=IF(ISERROR(AVERAGE(G2:G25)),"N/A",AVERAGE(G2:G25))
G29	=SUMIF(C2:C25,"nf",G2:G25)
G30	=IF(ISERROR(AVERAGEIF(E2:E25,"nf",G2:G25)),"N/A",AVERAGEIF(E2:E25,"nf",G2:G25))
G32	=SUMIF(C2:C25,"uf",G2:G25)
G33	=IF(ISERROR(AVERAGEIF(E2:E25,"uf",G2:G25)),"N/A",(AVERAGEIF(E2:E25,"uf",G2:G25)))
G35	=SUMIF(C2:C25,"nc",G2:G25)
G36	=IF(ISERROR(AVERAGEIF(E2:E25,"nc",G2:G25)),"N/A",(AVERAGEIF(E2:E25,"nc",G2:G25)))
G38	=SUMIF(C2:C25,"uc",G2:G25)
G39	=IF(ISERROR(AVERAGEIF(E2:E25,"uc",G2:G25)),"N/A",(AVERAGEIF(E2:E25,"uc",G2:G25)))
H26	=SUM(H2:H25)
H27	=IF(ISERROR(AVERAGE(H2:H25)),"N/A",AVERAGE(H2:H25))
H29	=SUMIF(C2:C25,"nf",H2:H25)
H30	=IF(ISERROR(AVERAGEIF(F2:F25,"nf",H2:H25)),"N/A",AVERAGEIF(F2:F25,"nf",H2:H25))
H32	=SUMIF(C2:C25,"uf",H2:H25)
H33	=IF(ISERROR(AVERAGEIF(F2:F25,"uf",H2:H25)),"N/A",(AVERAGEIF(F2:F25,"uf",H2:H25)))
J26	=SUM(E26:H26)
J27	=IF(ISERROR(AVERAGE(I2:I25)),"N/A",(AVERAGE(I2:I25)))
J29	=SUM(E29:H29)
J30	=IF(ISERROR(AVERAGEIF(C2:C25,"nf",I2:I25)),"N/A",(AVERAGEIF(C2:C25,"nf",I2:I25)))
J32	=SUM(E32:H32)
J33	=IF(ISERROR(AVERAGEIF(C2:C25,"uf",I2:I25)),"N/A",(AVERAGEIF(C2:C25,"uf",I2:I25)))
J35	=SUM(E35:H35)
J36	=IF(ISERROR(AVERAGEIF(C2:C25,"nc",I2:I25)),"N/A",(AVERAGEIF(C2:C25,"nc",I2:I25)))
J38	=SUM(E38:H38)
J39	=IF(ISERROR(AVERAGEIF(C2:C25,"uc",I2:I25)),"N/A",(AVERAGEIF(C2:C25,"uc",I2:I25)))

 

[Michael M]

Bob
Glad you got it sorted.
For what it's worth, I'd make the "N/A" something else.
#N/A is an error message in Excel functions and it could be misconstrued as a mistake in the spreadsheet, when in actual fact it is a "message"
Maybe something like

=IF(ISERROR(AVERAGE(E2:E25)),"No Avge",AVERAGE(E2:E25))

might be more appropriate.

Yeah, HTML maker has limits so that posters don't submit an entire spreadsheet. usually a small sample is adequate.