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

Offline Excalibur

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 508
  • Total likes: 1
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #780 on: January 29, 2017, 05:36:59 PM »
You want that the empty cells should be filled with zeros in all the sheets? Just do an IF formula, something like
Code: [Select]
=IF(Sheet1!A1="","",Sheet1!A1)

I get what you're saying, but I mean to copy the value from the formula directly as a value, instead after I insert it I copy and past the value and it populates throughout the sheets.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1819
  • Total likes: 235
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Problem
« Reply #781 on: January 29, 2017, 05:42:50 PM »
I get what you're saying, but I mean to copy the value from the formula directly as a value, instead after I insert it I copy and past the value and it populates throughout the sheets.
Not sure what you mean. I think you should explain exactly what you want to do and preferably with an example file.

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1555
  • Total likes: 61
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: USA
Re: Excel Problem
« Reply #782 on: January 29, 2017, 05:55:31 PM »
OK, I have another one:

Trying to calculating interest into the loan amount (Hard money), I can't figure how to do it correctly.

Say I take a $1,000 loan at 15% annually, for 2 years.
8% to be paid monthly, and (7*2)% to be paid as a balloon after 2 years.

Here is the question: I want to add the monthly 8% to the $1,000 upfront and escrow it, so that I don't need to make any payments on the loan over the life of the loan, only the balloon at the end.
But I can't just add $1000*8%*2 to the 1000, as the loan will charge 15% on the additional 8% as well...

Thanks!
I still need help with this...

Can't figure it out how much the initial amount needs to be to cover it's own interest.. is that even possible?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3357
  • Total likes: 539
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #783 on: January 29, 2017, 06:03:15 PM »
I still need help with this...

Can't figure it out how much the initial amount needs to be to cover it's own interest.. is that even possible?
Maybe explain in greater detail how the loan is structured, and what you're trying to change'accomplish.

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1555
  • Total likes: 61
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: USA
Re: Excel Problem
« Reply #784 on: January 29, 2017, 06:04:32 PM »
Maybe explain in greater detail how the loan is structured, and what you're trying to change'accomplish.
I may have got it.
I'll elaborate in a bit

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #785 on: January 29, 2017, 06:04:45 PM »
I still need help with this...

Can't figure it out how much the initial amount needs to be to cover it's own interest.. is that even possible?
You need to google to find a good free template

https://www.vertex42.com/ExcelTemplates/excel-amortization-spreadsheet.html
These are good

Offline Yammer

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2015
  • Posts: 3697
  • Total likes: 215
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #786 on: February 10, 2017, 11:30:27 AM »
I have an excel sheet that in the same column there are names, addresses and phone numbers. I would like to have them in separate columns. I tried a text to columns but there seems to be no way of splitting it when it hits a numerical value. ( The beginning of the address )

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12125
  • Total likes: 2173
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #787 on: February 10, 2017, 11:46:11 AM »
I have an excel sheet that in the same column there are names, addresses and phone numbers. I would like to have them in separate columns. I tried a text to columns but there seems to be no way of splitting it when it hits a numerical value. ( The beginning of the address )
you can do this in google sheets with regexextract. If you post one or two sample cells I can give you the formula to use.
Workflowy. You won't know what you're missing until you try it.

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 5997
  • Total likes: 1820
  • DansDeals.com Hat Tips 262
    • View Profile
Re: Excel Problem
« Reply #788 on: February 10, 2017, 11:59:58 AM »
I have an excel sheet that in the same column there are names, addresses and phone numbers. I would like to have them in separate columns. I tried a text to columns but there seems to be no way of splitting it when it hits a numerical value. ( The beginning of the address )

Text to columns with the delimiter being a space doesn't help either?
If it's not free shipping it's not worth it.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13062
  • Total likes: 7715
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #789 on: February 10, 2017, 12:03:36 PM »
I have mostly had success with similar things using this workaround

I use replace all to replace " 1" with ";1" and repeat for other numbers and use the same idea for each item (" NY" to ";NY")
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12125
  • Total likes: 2173
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #790 on: February 10, 2017, 12:05:00 PM »
I have mostly had success with similar things using this workaround

I use replace all to replace " 1" with ";1" and repeat for other numbers and use the same idea
time to learn regex then :)
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13062
  • Total likes: 7715
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #791 on: February 10, 2017, 12:05:36 PM »
time to learn regex then :)
Probably, but it doesn't happen that often.
Feelings don't care about your facts

Offline Yammer

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2015
  • Posts: 3697
  • Total likes: 215
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #792 on: February 10, 2017, 12:17:15 PM »
Text to columns with the delimiter being a space doesn't help either?
There are other spaces
I have mostly had success with similar things using this workaround

I use replace all to replace " 1" with ";1" and repeat for other numbers and use the same idea for each item (" NY" to ";NY")
Not all addresses start with a 1

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 13062
  • Total likes: 7715
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #793 on: February 10, 2017, 12:19:58 PM »
There are other spacesNot all addresses start with a 1
It is tedious but you can repeat with other numbers
Feelings don't care about your facts

Offline CZ60

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jul 2014
  • Posts: 431
  • Total likes: 38
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #794 on: February 10, 2017, 12:45:29 PM »
If you put a few examples, I can try to come up with a formula that will cover most of them..

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3357
  • Total likes: 539
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #795 on: February 10, 2017, 01:15:01 PM »
First step is to separate the names from the addresses/phone numbers. Assuming your text is in column A , the first formula gives you the name in one column by itself, and the second gives you the address and phone number together.

=LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))
=RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)

Separating out the pieces of the second formula will depend on how it's set up. You might be able to use text to columns there (like if the phone numbers start with a parenthesis).

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1819
  • Total likes: 235
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Problem
« Reply #796 on: February 10, 2017, 02:21:59 PM »
I have an excel sheet that in the same column there are names, addresses and phone numbers. I would like to have them in separate columns. I tried a text to columns but there seems to be no way of splitting it when it hits a numerical value. ( The beginning of the address )
If you have Excel 2016, not sure if 2013 too, you can do this with Flash Fill. Just do yourself a few examples and he'll usually be able to finish the rest.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #797 on: February 12, 2017, 12:19:52 PM »
If you have Excel 2016, not sure if 2013 too, you can do this with Flash Fill. Just do yourself a few examples and he'll usually be able to finish the rest.
2013 has it too. Sometimes it's awesome, and sometimes it just can't figure it out.

Offline JTZ

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Apr 2016
  • Posts: 1903
  • Total likes: 228
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #798 on: February 14, 2017, 05:06:58 PM »
How to subtract a group of cells from a fixed number?
My n00b attempt: =15000 - (B3:B10)
"LESS IS MORE" It is the cumulative effect that kills deals!!! How many times do I have to say this?  >:(

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12125
  • Total likes: 2173
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #799 on: February 14, 2017, 05:18:17 PM »
How to subtract a group of cells from a fixed number?
My n00b attempt: =15000 - (B3:B10)
Do you want to first total B3:B10, and then subtract that from 15000?
If so:
=15000-SUM(B3:B10)

Workflowy. You won't know what you're missing until you try it.