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

Offline Work-for-ur-muny

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2014
  • Posts: 3101
  • Total likes: 317
  • DansDeals.com Hat Tips 34
    • View Profile
Re: Excel Problem
« Reply #760 on: January 23, 2017, 05:25:21 PM »
Thanks guys!!!

Offline syp5

  • Dansdeals Gold Elite
  • ***
  • Join Date: May 2008
  • Posts: 163
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #761 on: January 24, 2017, 03:50:12 PM »
Is there a way to upload a spreadsheet of (names and amounts) to quick-books to print several hundred checks of different amounts?
Ty

Offline Boruch999

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2014
  • Posts: 1896
  • Total likes: 139
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #762 on: January 24, 2017, 04:01:40 PM »
Is there a way to use variables in Excel, that is to set a certain value to x and then call x repeatedly throughout the sheet?

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5183
  • Total likes: 587
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #763 on: January 24, 2017, 04:10:37 PM »
Is there a way to use variables in Excel, that is to set a certain value to x and then call x repeatedly throughout the sheet?
Certainly. Very straight forward process.

Input your x into any field (simplest would be on a a secondary sheet), then you put that cell reference into your formula.

« Last Edit: January 24, 2017, 04:13:42 PM by lubaby »

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 #764 on: January 24, 2017, 04:13:20 PM »
Is there a way to use variables in Excel, that is to set a certain value to x and then call x repeatedly throughout the sheet?
Use a cell to enter the variable and keep referencing the cell. You can even give the cell a name and reverence it that way.
Feelings don't care about your facts

Offline Boruch999

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2014
  • Posts: 1896
  • Total likes: 139
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #765 on: January 24, 2017, 04:23:06 PM »
Use a cell to enter the variable and keep referencing the cell. You can even give the cell a name and reverence it that way.
Certainly. Very straight forward process.

Input your x into any field (simplest would be on a a secondary sheet), then you put that cell reference into your formula.



I feel silly.  Thanks guys!

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5183
  • Total likes: 587
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #766 on: January 24, 2017, 04:26:40 PM »
I feel silly.  Thanks guys!
No problem  :)

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 #767 on: January 24, 2017, 04:39:06 PM »
I don't know what to title this problem..

See the screenshot (Or excel file). My input is on the right, what I want to output is on the left.

The problem is that Column A's types, or number of types, are not consistent. sometimes it is "version A/B/D" or "first/second/5th" etc, and right now I need to manually enter the list of types, and sometimes I'll miss one that only appears once etc.

What I would like to know is if there is some way to have the column A in the results side automatically generate a row for every type in the dataset, and then the rest is simple...

Thanks!

ETA: Trying this
« Last Edit: January 24, 2017, 04:49:30 PM by Zalc »

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5183
  • Total likes: 587
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #768 on: January 24, 2017, 04:53:25 PM »
I don't know what to title this problem..

See the screenshot (Or excel file). My input is on the right, what I want to output is on the left.

The problem is that Column A's types, or number of types, are not consistent. sometimes it is "version A/B/D" or "first/second/5th" etc, and right now I need to manually enter the list of types, and sometimes I'll miss one that only appears once etc.

What I would like to know is if there is some way to have the column A in the results side automatically generate a row for every type in the dataset, and then the rest is simple...

Thanks!

ETA: Trying this
Array Formula this (Ctrl+Shift+Enter) after pasting the formula.
Code: [Select]
=IFERROR(INDEX($A$1:$A$100, MATCH(0,COUNTIF($A$1:A1, $A$1:$A$100), 0)),"")Just modify the 100 in the formula to however many rows your workbook is.

That article describes it pretty well.
« Last Edit: January 24, 2017, 05:02:07 PM by lubaby »

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 #769 on: January 24, 2017, 05:13:19 PM »
Worked Great! thanks!

Now I need to sit down and understand it     :o

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 #770 on: January 25, 2017, 07:44:39 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!
« Last Edit: January 25, 2017, 07:50:25 PM by Zalc »

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 #771 on: January 26, 2017, 12:49:29 AM »
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!
Used Goal Seeker to do it.

GIYF

Offline lechatchileh ariber

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Mar 2013
  • Posts: 895
  • Total likes: 24
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #772 on: January 27, 2017, 11:14:24 AM »
Can I have an array pulling data in a different row?
Right now I'm using
=arrayformula(if(A:A >0, A:A&" "&B:B&" "&C:C&" "&E:E, ))
And a) I have to put it in column N
B) it pulls it in the same row in which a is more than 0.
Is there a way that I can have this script in row 27 (the data starts in row 47) and it will only pull the first 19 results?
Eta: I'm using Google sheets.
I don't sin, I give myself opportunities to repent.

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 #773 on: January 29, 2017, 03:52:33 PM »
So, does anyone know how to extrapolate from a line on one excel workbook to another across multiple 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 #774 on: January 29, 2017, 03:55:30 PM »
So, does anyone know how to extrapolate from a line on one excel workbook to another across multiple sheets?
Can you please explain what you are trying to do?

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 #775 on: January 29, 2017, 04:34:34 PM »
I have a P&L with many line items, I'm creating a projection. The projection is broken into 1 sheet for each month

I need cells h50-af50 copied and pasted into another workbook h50 on sheet 1 in e 50; j50 on sheet 2 e 50, L50 on sheet 3 in e 50and so on.

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 #776 on: January 29, 2017, 04:39:21 PM »
I have a P&L with many line items, I'm creating a projection. The projection is broken into 1 sheet for each month

I need cells h50-af50 copied and pasted into another workbook h50 on sheet 1 in e 50; j50 on sheet 2 e 50, L50 on sheet 3 in e 50and so on.
Select all the sheets and then write your formula in one of them. It will populate them all

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 #777 on: January 29, 2017, 05:17:13 PM »
I'll give it a shot, thanks

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 #778 on: January 29, 2017, 05:26:00 PM »
I'm taking the total number and creating a formula to divide by twelve, however ideally I would like to be able to pull the exact numbers, and I still haven't figured out how to do that...
« Last Edit: January 29, 2017, 05:35:47 PM by Excalibur »

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 #779 on: January 29, 2017, 05:29:57 PM »
Is there a way to do this but copy in only values?
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)