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]

I think I understand about data validation, but the these are actually categories that these deals fall into that can not be derived from any of the information we are using on this spread sheet. I just made that column instead of creating 4 different sheets.

Something that is happening that I can see is that if you put any values into the rows and you go back and remove them, for example a customer changes his mind and we rollback his information, the sum column (H) will now show $0.00 instead being blank.

 

[Michael M]

Hmm, Data validation is a drop down list that lets you select your Cat rather than typing in from the list in the last column, it doesn't have to be derived from anything else on the sheet.
You might be better using BLANK in the cell if it is either blank or 0, is there a particular reason why it has to be either ?

=IF(AND(C2:F2="",C2:F2=0),"",SUM(C2:F2))

 

[Bob Barker]

I guess the only reason I would like it blank is that when I set the total average (I27) it will only factor in the cells that have $$ amounts that are attached to customers. If we take out the customer information and there is still $0.00 in the total column it will skew the average.

I also thinks it just looks cleaner only having data when it is pertaining to a customer.

I misunderstood what data validation is and have set it up. It is better than having the menu on the side.

 

[Michael M]

You could replace your average with

=SUM(G2:G15)/COUNTIF(G2:G15,">0")

It then won't matter if there are zeros.

 

[Bob Barker]

A customer can come in and sign up with us and still not buy any of these items. In that case, he would need to be factored into the total average even though he spent $0.00. Wouldn't having a $0.00 amount in that column even after we deleted all the information, including the name, stk# and cat, causes the average to be wrong.

I am appreciative of all your help. This is light years ahead of where I was earlier.

I guess one other topic is this, in the F column (Gap), I need the average value at F27 to only factor in the average on deals that have the Cat designation of "nf" or "nc". How would you setup that formula?

 

[Bob Barker]

I noticed that when deleting the entries using the space bar, that is when the $0.00 shows up in the totals. If you select the cell and delete at the data entry bar, it works like I want it to. Is there a way that it can clear the cell with the space bar in the same fashion as deleting it in the formula bar?

 

[Bob Barker]

Question about conditional formatting

In my spreadsheet, I am trying to use conditional formatting to gray out cells in column G when the value "NC" or "UC" is selected in column B for that row. In essence, I do not want any value input at all and I thinking greying it out will keep people from putting a value there if they select "NC" or "UC" from the drop down menu for that row. Of course if there is a way to get it to grey out AND lock the cell from any input at the same time, that would be nice too.

 

[Michael M]

Re: Question about conditional formatting

Bob
Post #14 allows for zeros to be in the list but they won't affect the average.

I wouldn't recommend using the space for the simple fact that by pressing space to delete a value means the cell isn't now empty.....it contains a space...there is a difference.
Finally, I think we should solve the other "stuff" before we move on to post #17..?

 

[Bob Barker]

Hi Mike!

Thanks to your excellent advice I believe that I have a workable spreadsheet now. I would like to post what I have completed for you to look at, but the HTML won't go over 30 lines?

 

[Bob Barker]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		Customer	Cat	Stk#	SC	P&F	RS	GAP	Total	Venue
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Here is the spreadsheet part