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 38618 times)

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6335
  • Total likes: 68
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #60 on: January 09, 2014, 03:30:31 PM »
is it always after the last space in the cell?
it was, what's the eitza for that?

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 #61 on: January 13, 2014, 01:07:31 PM »
it was, what's the eitza for that?
this formula
Quote
=IF(ISERROR(FIND(" ",A1)),A1, RIGHT(A1,LEN(A1) - FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

by replacing "A1" with the correct cell reference
also you can replace the
Quote
" "
(the reference to spaces) with any character you want and it'll pull all data after the last occurrence of that character
Basically its a reverse find function

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24251
  • Total likes: 61
  • DansDeals.com Hat Tips 11
  • 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 #62 on: January 21, 2014, 04:16:30 PM »
How would I create a starwood calculator, such that if I say I need, say 80,000 miles, it'd spit out to transfer 65,000 SPG?
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 BAHayman

  • Administrator
  • Dansdeals Lifetime Platinum Elite
  • **********
  • Join Date: Mar 2010
  • Posts: 1934
  • Total likes: 6
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #63 on: January 21, 2014, 04:45:09 PM »
How would I create a starwood calculator, such that if I say I need, say 80,000 miles, it'd spit out to transfer 65,000 SPG?
Something like this?
Code: [Select]
=(ROUNDDOWN(A1/25000)*20000)+MOD(A1, 25000)

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24251
  • Total likes: 61
  • DansDeals.com Hat Tips 11
  • 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 #64 on: January 21, 2014, 04:53:51 PM »
Something like this?
Code: [Select]
=(ROUNDDOWN(A1/25000)*20000)+MOD(A1, 25000)

Ding, ding, ding. Gracias.

Though it had to be slightly modified:

Code: [Select]
=ROUNDDOWN(T10/25000,0)*20000+MOD(T10,25000)
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 BAHayman

  • Administrator
  • Dansdeals Lifetime Platinum Elite
  • **********
  • Join Date: Mar 2010
  • Posts: 1934
  • Total likes: 6
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #65 on: January 21, 2014, 04:58:20 PM »
Ding, ding, ding. Gracias.

Though it had to be slightly modified:

Code: [Select]
=ROUNDDOWN(T10/25000,0)*20000+MOD(T10,25000)
Ah, good to know. In Google Spreadsheets the second parameter for ROUNDDOWN is optional, which is why I use that over FLOOR(<value>, 1).

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24251
  • Total likes: 61
  • DansDeals.com Hat Tips 11
  • 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 #66 on: January 21, 2014, 04:59:08 PM »
Figured you might have been using Sheets.

For anyone who is curious:

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 churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6335
  • Total likes: 68
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #67 on: February 27, 2014, 07:26:20 PM »
Just closed an unsaved workbook  >:( >:(

I go to the recovery location and there is a weird file there,  I try opening in excel but it just opens a blank spreadsheet with no cells.    Is there any hope?

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 #68 on: February 28, 2014, 12:54:25 AM »
Just closed an unsaved workbook  >:( >:(

I go to the recovery location and there is a weird file there,  I try opening in excel but it just opens a blank spreadsheet with no cells.    Is there any hope?
can you find the auto saved version?
Should be something like file/recent/recover unsaved workbooks

Offline yitzf

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2458
  • Total likes: 3
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #69 on: February 28, 2014, 01:01:08 AM »
can you find the auto saved version?
Should be something like file/recent/recover unsaved workbooks

In 2013 its Backstage>Info>Versions

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6335
  • Total likes: 68
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #70 on: February 28, 2014, 12:27:51 PM »
can you find the auto saved version?
Should be something like file/recent/recover unsaved workbooks
It turns out that if the workbook was never saved/named it doesnt auto save as a different versions. It doesnt have a version of a unnamed doc...
I went to the C:\Users\CBC\AppData\Roaming\Microsoft\Excel\ the file wasnt there....

Anywho, I did the work again, better than version one  :)

Offline katherine123

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Oct 2013
  • Posts: 344
  • Total likes: 0
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: USA
Excel help
« Reply #71 on: April 07, 2014, 09:15:48 PM »
I'm trying to make address labels but when I merge in word it keeps dropping the "0" in zip codes starting with "0". I can't find a fix online- does anyone know what I need to do?
« Last Edit: April 07, 2014, 09:20:12 PM by katherine123 »

Offline chucksterace

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6798
  • Total likes: 3
  • DansDeals.com Hat Tips 40
  • Gender: Male
    • View Profile
    • Flight Lynx
  • Location: Chicago, Jerusalem
  • Programs: AA Exec Plat; UA Premier 1K; ; LY Plat; Hyatt Platinum; Hertz President's Circle; Avis President's Club
Re: Excel help
« Reply #72 on: April 07, 2014, 09:18:00 PM »
I just add a ' anytime that happens to me..

And posting in already existing thread may help - http://forums.dansdeals.com/index.php?topic=35830.60. Please search before opening a new thread.
« Last Edit: April 07, 2014, 09:21:11 PM by chucksterace »
You may not hold me responsible or liable for any actions taken that was recommended from my account or username.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8369
  • Total likes: 109
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel help
« Reply #73 on: April 07, 2014, 09:18:54 PM »
I just add a ' anytime that happens to me
+1
Workflowy. You won't know what you're missing until you try it.

Offline katherine123

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Oct 2013
  • Posts: 344
  • Total likes: 0
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: USA
Re: Excel help
« Reply #74 on: April 07, 2014, 09:19:48 PM »
I just add a ' anytime that happens to me

Won't that print as '08701? That doesn't look right...