Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

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

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • 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: 4530
  • Total likes: 283
  • 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: 25424
  • Total likes: 723
  • DansDeals.com Hat Tips 15
  • 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: 1939
  • Total likes: 28
  • 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: 25424
  • Total likes: 723
  • DansDeals.com Hat Tips 15
  • 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: 1939
  • Total likes: 28
  • 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: 25424
  • Total likes: 723
  • DansDeals.com Hat Tips 15
  • 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: 7355
  • Total likes: 301
  • 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: 4530
  • Total likes: 283
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2623
  • Total likes: 53
  • 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: 7355
  • Total likes: 301
  • 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: 6880
  • Total likes: 19
  • DansDeals.com Hat Tips 40
  • Gender: Male
    • View Profile
    • LYNX IT Consulting
  • 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 for any actions taken that were recommended from my account or username.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12879
  • Total likes: 3368
  • 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...

Offline chucksterace

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6880
  • Total likes: 19
  • DansDeals.com Hat Tips 40
  • Gender: Male
    • View Profile
    • LYNX IT Consulting
  • 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 #75 on: April 07, 2014, 09:22:05 PM »
Won't that print as '08701? That doesn't look right...

Don't think so. Just did a test run, print to PDF and came out good

ETA: Or you can go add a custom number, (where you would choose date, number, currency, etc) and just put in "00000" that way the minimum in the box would be five numbers and the zero would show.
You may not hold me responsible for any actions taken that were recommended from my account or username.

Offline myb821

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Mar 2011
  • Posts: 7570
  • Total likes: 28
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: New York
Re: Excel help
« Reply #76 on: April 07, 2014, 09:23:09 PM »
or set it as text

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12879
  • Total likes: 3368
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel help
« Reply #77 on: April 07, 2014, 09:23:26 PM »
Won't that print as '08701? That doesn't look right...
Nope - I do it all the time. Funny how this issue only comes up with lakewood :)
Workflowy. You won't know what you're missing until you try it.

Offline chucksterace

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6880
  • Total likes: 19
  • DansDeals.com Hat Tips 40
  • Gender: Male
    • View Profile
    • LYNX IT Consulting
  • 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 #78 on: April 07, 2014, 09:24:08 PM »
Nope - I do it all the time. Funny how this issue only comes up with lakewood :)

Oh well. Good thing I don't live in lakewood  :P
You may not hold me responsible for any actions taken that were recommended from my account or username.

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 #79 on: April 07, 2014, 09:24:25 PM »
Thank you all, it worked! Two nights of frustration solved within 5 minutes thanks to DDF.