Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

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

Online churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6516
  • Total likes: 79
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #45 on: January 07, 2014, 01:09:57 PM »
Is excel case sensitive?
The word "cost" in your formula is lower case.
Not here

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24332
  • Total likes: 67
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« Reply #46 on: January 07, 2014, 01:20:08 PM »
forgot the ctr shift

When I do the CTRL+SHIFT+ENTER:

2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Online skyguy918

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2437
  • Total likes: 27
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #47 on: January 07, 2014, 01:23:12 PM »
When I do the CTRL+SHIFT+ENTER:


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.

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24332
  • Total likes: 67
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« Reply #48 on: January 07, 2014, 01:28:39 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?
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24332
  • Total likes: 67
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« 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:

2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Online skyguy918

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2437
  • Total likes: 27
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #50 on: January 07, 2014, 01:51:27 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).

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24332
  • Total likes: 67
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« 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!
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Online skyguy918

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2437
  • Total likes: 27
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #52 on: January 07, 2014, 01:54:17 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.

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4388
  • Total likes: 0
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #53 on: January 07, 2014, 02:42:49 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

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 2945
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #54 on: January 07, 2014, 02:53:42 PM »
absolutely, I don't know how I'd manage now without vba
+1
There's so much more you can do with it.

Offline noturbizniss

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2012
  • Posts: 6167
  • Total likes: 31
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
  • Location: North Jersey
Re: Excel Problem
« Reply #55 on: January 07, 2014, 03:33:02 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.
READ THE DARN WIKI!!!!

Chuck Norris...
...can still do FT method
...READS THE WIKI!!!

Online churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6516
  • Total likes: 79
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« 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

Online lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3474
  • Total likes: 70
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Problem
« Reply #57 on: January 08, 2014, 03:10:23 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
Once the game is over, the king and the pawn go back in the same box.

Online churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6516
  • Total likes: 79
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« 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.  :)

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4388
  • Total likes: 0
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #59 on: January 09, 2014, 03:25:56 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?