Author Topic: Excel Help and Problems (Read 353027 times)

You have to put the cell that you're doing the summing in outside of the range you're summing.

ETA: Either add a row or column at the beginning of the sheet or put the formula on another sheet.

AJK · « **Reply #48 on:** January 07, 2014, 01:28:39 PM »

Quote from: skyguy918 on January 07, 2014, 01:23:12 PM

You have to put the cell that you're doing the summing in outside of the range you're summing.

ETA: Either add a row or column at the beginning of the sheet or put the formula on another sheet.

The cell I'm summing in is F3, which is outside A1:E15, no?

AJK · « **Reply #49 on:** January 07, 2014, 01:34:34 PM »

Anyway, I moved it to the column before and it worked on the test sheet.

But when running it on the real sheet:

skyguy918 · « **Reply #50 on:** January 07, 2014, 01:51:27 PM »

Quote from: AJK on January 07, 2014, 01:34:34 PM

Anyway, I moved it to the column before and it worked on the test sheet.

But when running it on the real sheet:

The reason it wasn't working on the test sheet is because even though F3 is outside of A1:E15, it's inside OFFSET(A1:E15,0,1), which is equivalent to B1:F15.

I'm assuming on the real sheet, the range you're using is much bigger than the 75 cells you referenced in the test sheet? If it's really big you might not be able to handle it within Excel itself, but VBA will be able to do it no problem (it may take some time to run though).

AJK · « **Reply #51 on:** January 07, 2014, 01:52:48 PM »

Yeah, it's >75 but <1000 cells.

I guess I'm stuck with VBA. Or using multiple formulas. Thanks for all your help!

skyguy918 · « **Reply #52 on:** January 07, 2014, 01:54:17 PM »

Quote from: AJK on January 07, 2014, 01:52:48 PM

Yeah, it's >75 but <1000 cells.

I guess I'm stuck with VBA. Or using multiple formulas. Thanks for all your help!

Everybody starts out being scared of VBA. Don't worry, that goes away pretty quickly with exposure to it.

smurf · « **Reply #53 on:** January 07, 2014, 02:42:49 PM »

Quote from: skyguy918 on January 07, 2014, 01:54:17 PM

Everybody starts out being scared of VBA. Don't worry, that goes away pretty quickly with exposure to it.

absolutely, I don't know how I'd manage now without vba

AnonymousUser · « **Reply #54 on:** January 07, 2014, 02:53:42 PM »

Quote from: smurf on January 07, 2014, 02:42:49 PM

absolutely, I don't know how I'd manage now without vba

+1
There's so much more you can do with it.

noturbizniss · « **Reply #55 on:** January 07, 2014, 03:33:02 PM »

Quote from: skyguy918 on January 07, 2014, 01:51:27 PM

The reason it wasn't working on the test sheet is because even though F3 is outside of A1:E15, it's inside OFFSET(A1:E15,0,1), which is equivalent to B1:F15.

I'm assuming on the real sheet, the range you're using is much bigger than the 75 cells you referenced in the test sheet? If it's really big you might not be able to handle it within Excel itself, but VBA will be able to do it no problem (it may take some time to run though).

Shouldn't matter that f3 is in the offset range unless he actually has a cell with "cost" in row e. I got it with no issue.

churnbabychurn · « **Reply #56 on:** January 08, 2014, 02:54:22 PM »

Whats the best way to deliminate or separate numbers from a text and numbers cell?
The left and right functions cant work well cuz the numbers are not consistent. Plus I want to remove the numbers from the text string. TIA

E.G.

Quote

Advertising 8,340.14
Alarm 4,060.00
Automobile Expense 3,505.76
Bad Debt Expense 360.00
Bank Service Charges 1,916.83
CC Discount-Settlement 11,832.16

lubaby · « **Reply #57 on:** January 08, 2014, 03:10:23 PM »

Quote from: churnbabychurn on January 08, 2014, 02:54:22 PM

Whats the best way to deliminate or separate numbers from a text and numbers cell?
The left and right functions cant work well cuz the numbers are not consistent. Plus I want to remove the numbers from the text string. TIA

E.G.

I've gotten this with some basic Google searching. Only issue is its not recognizing the decimal points.

This pulls the number out.
{=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)}
Post #6

This pulls the text.
=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))
Post #3

churnbabychurn · « **Reply #58 on:** January 08, 2014, 03:22:21 PM »

Worked like a charm, TY! (I did Google first but it was confusing)
For adding back the decimal I just added a column and divided by 100.

smurf · « **Reply #59 on:** January 09, 2014, 03:25:56 PM »

Quote from: churnbabychurn on January 08, 2014, 02:54:22 PM

Whats the best way to deliminate or separate numbers from a text and numbers cell?
The left and right functions cant work well cuz the numbers are not consistent. Plus I want to remove the numbers from the text string. TIA

E.G.

is it always after the last space in the cell?

Topic Wiki

Author Topic: Excel Help and Problems (Read 353027 times)