I like to work with really large data sets in Excel. Is there anything I can do to minimize the chances of Excel crashing when I do this?

I ran into another excel problem I could use some help with.


Here's a simplified version of what I'm having problems with:

A4, B4, and C4 will be where I enter the SUM formula to total the cells above them. The problem I have is I need the cell in C4 to be blank if there are no numbers entered in C1:C3. Even though I want the cell to look blank if there are no numbers to total, I need the formula to be in there so when numbers are entered it will automatically calculate the total and show that total.


I then need to average the totals in A4, B4, and C4. I only want to average totals with numbers (so I am using an AVERAGEIF formula). In the example above, it should only average A4 (15) and B4 (30) for an average of 22.5. Then as soon as numbers are entered in C1:C3, I'd like the total to automatically show in C4 and then also automatically be included in the average shown in D4.

The problem I am having is that whatever formula I use in A4, B4, and C4 to total those cells up, it will show a 0 if there are no numbers in the cells above them. If there is a 0 result, then my averageif formula will include this 0 in the average amount in D4.



(Some sums in A4, B4, C4 might actually be 0 so for those instances, that 0 result should be included in the averages)





Whew...I hope I explained that well enough to understand



Anyone have any idea to accomplish what I need?




Thanks!

I tried the Google, but ran into an issue so here goes:

I have a huge list of names, most containing First Middle Last with a bunch of Suffixes (all in one cell) thrown in. I need to sort all alphabetically by last name, suffixes excluded.

What's the best way to do this?

Have you tried "Text to columns"?

Select the data containing the names. Go to the data tab, then "Text to Columns". That should help you separate the names in three columns. Then you can sort by last name.

If I’m understanding the question properly

Cell C where you want the blanks can use one of the following options (and likely 100 other solutions)

+iferror(sum(C$1:C$3),””)
+if(sum(C$1:C$3)”>0”, sum(C$1:C$3),””)



Sent from my iPhone using Tapatalk

^ Awesome. That worked perfectly.

Thanks!

Sent from my iPhone using Tapatalk

I guess I spoke a little too soon. That worked for part of what I need. I have two sets of data I'm having the above issue with. The first set is just a list of numbers. The above formula worked perfectly to resolve the issue I was having with just numbers.

I also have another set of dollar amounts. I tried to use the same formula listed above for those dollar amounts, but when I use that formula it affects my averages.

Here's what it looks like if I use that new formula with dollar amounts.

Would I need to change something in the formula you provided to get it to work with dollar amounts?


Thanks!

A bit clunky, but i think this should work:

- insert this formula in 3 new cells (one for each total A4, B4 and C4) : =IF(A4 < > 0, 1, 0)

- in D4, use this formula: = SUM(A4:C4)/SUM(3 new cells)

Thanks sumey, but I don't understand.

Where would I put the formula you mentioned: =IF(A4 < > 0, 1, 0)? Would I need to add cells to put this formula in? I'd really prefer not to have to put new cells and it will junk up my spreadsheet.

Would you, or anyone else have a suggestion that will get this to owrk without having to add new cells?

THANKS!

If you're limited to 3 columns, then you can put this in D4.

=SUM(A4:C4)/(IF(A4< >0,1,0) + IF(B4< >0,1,0) + IF(C4< >0,1,0))

(Remove the spaces between < and >)

If you want the flexibility of any number of columns then there i am sure there is a more elegant way. I'll have a think.

I'm not at home to test it, but how about this in D4?

= SUM(A4:C4)/COUNTIF(A4:C4,"< > 0")

Remove space between < and >

64 bit version allowed me to pivot an 850mb file.

I am trying to do a simple formula to multiply quantity by cost and I keep getting this error. I tried removing the spaces and formatting but keep getting it. I don't know how to upload the spreadsheet but I copied and pasted what it looks like. I know it is a longshot but any ideas what is wrong? I do these formulas all day and don't know why this one isn't working.

Quantity Cost
3675 $0.39* #VALUE!
2213 $0.93* #VALUE!
1555 $0.99* #VALUE!

Not sure where the asterisk next to your cost came from, but it might be turning the cost into text instead of a number, leading to the error.

I tried: = SUM(K39:O39)/COUNTIF(K39:O39,">-1")

I wanted to include sums that were 0 in my averages as some sales days will be 0 and need to count towards the average. So I changed your formula to >-1 instead of <>0.

This works for the average in column D. The problem I am having is I don't know what formula to use in A4, B4, C4 that will sum the three rows above them, but won't add a 0 at the bottom if nothing has been entered into those cells yet.

Once A1,A2,A3 have been entered, I'd like A4 to automatically total those cells and that new total count in the average in D4. But, whatever formula I try (and I don't know many), puts a 0 in A4 even if nothing is entered in A1:A3.

What formula can I use in A4 that will total up A1:A3, but ONLY total A1:A3 IF numbers have been entered into A1:A3?

Thanks!!!!

Are quantity and cost in the same cell?

If so use text to columns and use $ as your deliminator

You can also use the left or right functions to extract the data but that’s less likely to be useful for scaling data of the number of digits you’re looking to extract is different per cell.

As an example you could do in cell a3: +left(cell number, 4) then in a4 +right(cell number, 4). Then in a5 +a3*a4

Also make sure your values are formatted as numbers or accounting or some other similar type.

Sent from my iPhone using Tapatalk

Put this in A4:

=IF(SUMPRODUCT(--(A1:A3 < > "")) =0,-1,SUM(A1:A3))

Repeat in the other columns. Remove space between < and >

ETA: you can replace the -1 in the formula with ""

Actually **** that doesn't work. I'll try again.

Ok, here's a solution that (I'm pretty sure) works!

- Cell A4: = IF(SUMPRODUCT(--(A1:A3< >""))=0,-1,SUM(A1:A3)) (remove space between < and >)
- Repeat in B4 and C4
- Cell D4: =SUMIFS(A4:C4,A4:C4,">-1")/COUNTIF(A4:C4,">-1")
- Apply conditional formatting to A4-C4 to make -1 cells white text if you don't like seeing them in the empty columns

Ok, thanks for the help. There was actually a space in there that didn't get removed when I did text to columns, so I used the left function and it worked.

2+2 comes through again.

I am using libra office but if I knew how to do this in excel it may be easier for me. Any help appreciated. Thanks.

Avg(Ace) = Avg of Columns X and Y and Z only using rows with As,Ad,Ah,Ac
Note: so I end up with 3 averages of columns X, Y, Z

Example:
Avg of Column X = Avg(X1 + X3 + X4 ...)
X2 is a Kh so it is not included
XL is a 2s so it is not included

I am not very good at explaining things.
Thanks again for any help.

1) User a helper column to identify whether or not a column has an A e.g. =IFERROR(FIND("A",A1,1)^0,0) which will return a 1 if there's a 0 in A1 and a 0 if not.

2) Use AVERAGEIFS where you average the column of interest and condition on the helper column being equal to 1

Thanks. That is perfect.