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

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3809
  • Total likes: 822
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1320 on: October 08, 2019, 09:56:47 AM »
For a series of even 20 cash flows from a non compounding loan payments (p&I)every two weeks, xirr is giving very different results then IRR*20

Is xirr giving the effective yield which is incorrect here because it's simple interest?
The conversion is not XIRR = IRR * 20. It's XIRR = (1+IRR)^(365/14). The fact that it's 20 cash flows is irrelevant - the fact that each period is 14 days is relevant.

Not sure what how simple interest enters into this. XIRR gives the annualized effective yield, which is a compound interest value. IRR returns the yield per period. Unlike XIRR, IRR has no way of knowing the length of each period, and therefore has to solve for the per period value, with no way of converting it to annualized.

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 Help and Problems
« Reply #1321 on: October 08, 2019, 10:35:31 AM »


The conversion is not XIRR = IRR * 20. It's XIRR = (1+IRR)^(365/14). The fact that it's 20 cash flows is irrelevant - the fact that each period is 14 days is relevant.

Not sure what how simple interest enters into this. XIRR gives the annualized effective yield, which is a compound interest value. IRR returns the yield per period. Unlike XIRR, IRR has no way of knowing the length of each period, and therefore has to solve for the per period value, with no way of converting it to annualized.

I understand all that. My question is whether or not it is correct to use XIRR to assess returns in this scenario. - xirr is returning a compound interest value, but the loan in question is not compound interest.

So when an investor asks "what is my irr", what is the correct answer?

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 Help and Problems
« Reply #1322 on: October 08, 2019, 10:37:33 AM »

I understand all that. My question is whether or not it is correct to use XIRR to assess returns in this scenario. - xirr is returning a compound interest value, but the loan in question is not compound interest.

So when an investor asks "what is my irr", what is the correct answer?
If I lend money at fixed 5% interest and the payments are fully amortized over 6 months for eg. Should I use XIRR on the CF , or is the return simply 5% for 6 months?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3809
  • Total likes: 822
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1323 on: October 08, 2019, 01:18:50 PM »

I understand all that. My question is whether or not it is correct to use XIRR to assess returns in this scenario. - xirr is returning a compound interest value, but the loan in question is not compound interest.

So when an investor asks "what is my irr", what is the correct answer?
If I lend money at fixed 5% interest and the payments are fully amortized over 6 months for eg. Should I use XIRR on the CF , or is the return simply 5% for 6 months?
It doesn't matter how the underlying CF's were set up. IRR is definitionally a compounded rate:
Quote from: https://en.wikipedia.org/wiki/Internal_rate_of_return#Definition
The internal rate of return on an investment or project is the "annualized effective compounded return rate" or rate of return that sets the net present value of all cash flows (both positive and negative) from the investment equal to zero.

Offline isabels55

  • DansDeals Copper Elite
  • *
  • Join Date: Sep 2019
  • Posts: 2
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Chicago
Re: Excel Help and Problems
« Reply #1324 on: October 09, 2019, 02:30:49 AM »
I've had such problems last week.

Offline Therebbesbocher

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jun 2017
  • Posts: 111
  • Total likes: 14
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Help and Problems
« Reply #1325 on: October 25, 2019, 12:02:16 AM »
what kind of formula would I need if I want conditions of multiple columns to add to a total? for example, if column A is more than 10 add 1 to the total, and if its less than 5 subtract 1 from the total.
TIA

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2162
  • Total likes: 365
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1326 on: October 25, 2019, 12:08:20 AM »
what kind of formula would I need if I want conditions of multiple columns to add to a total? for example, if column A is more than 10 add 1 to the total, and if its less than 5 subtract 1 from the total.
TIA
This is a simple nested IF. Like (if the total is on column F...)
Code: [Select]
=IF(A1>10,F1+1,IF(A1<5,F1-1,"NOT DEFINED"))

Offline Therebbesbocher

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jun 2017
  • Posts: 111
  • Total likes: 14
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Help and Problems
« Reply #1327 on: October 25, 2019, 12:41:22 AM »
This is a simple nested IF. Like (if the total is on column F...)
Code: [Select]
=IF(A1>10,F1+1,IF(A1<5,F1-1,"NOT DEFINED"))
thanks, but I tried using that but it's not working, I think because once a nestled IF reaches a "true" it stops reading so it never reaches the next column. (unless I'm mistaken)

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2162
  • Total likes: 365
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1328 on: October 25, 2019, 12:55:18 AM »
thanks, but I tried using that but it's not working, I think because once a nestled IF reaches a "true" it stops reading so it never reaches the next column. (unless I'm mistaken)
Do you need something like?
Code: [Select]
=SUM(IF(A1>10,A1+1,IF(A1<5,A1-1,"NOT DEFINED")),(IF(B1>10,B1+1,IF(B1<5,B1-1,"NOT DEFINED"))),(IF(C1>10,C1+1,IF(C1<5,C1-1,"NOT DEFINED"))))

Offline Therebbesbocher

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jun 2017
  • Posts: 111
  • Total likes: 14
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Help and Problems
« Reply #1329 on: October 25, 2019, 01:32:58 AM »
Do you need something like?
Code: [Select]
=SUM(IF(A1>10,A1+1,IF(A1<5,A1-1,"NOT DEFINED")),(IF(B1>10,B1+1,IF(B1<5,B1-1,"NOT DEFINED"))),(IF(C1>10,C1+1,IF(C1<5,C1-1,"NOT DEFINED"))))

It does, but I removed the reference to the cell by the "value if true" because I want the total in the new column (where I putting the code), does that make sense?
it also comes back as #value if there is a number in between 5-10, anyway for it to just not change the total?

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12855
  • Total likes: 3302
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1330 on: October 25, 2019, 07:57:58 AM »
It does, but I removed the reference to the cell by the "value if true" because I want the total in the new column (where I putting the code), does that make sense?
it also comes back as #value if there is a number in between 5-10, anyway for it to just not change the total?
If it's between 5 and 10, you'll want to refer to the column from where it should copy the number in that case.
Workflowy. You won't know what you're missing until you try it.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2162
  • Total likes: 365
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1331 on: October 25, 2019, 11:29:29 AM »
It does, but I removed the reference to the cell by the "value if true" because I want the total in the new column (where I putting the code), does that make sense?
it also comes back as #value if there is a number in between 5-10, anyway for it to just not change the total?
Like this?
Code: [Select]
=SUM(IF(A1>10,1,IF(A1<5,-1,0)),(IF(B1>10,1,IF(B1<5,-1,0))),(IF(C1>10,1,IF(C1<5,-1,0))))

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1332 on: October 30, 2019, 11:08:54 AM »
Been trying to figure out a simple way to have a variable number (1-9) as part of an IF statement. Maybe someone here can help.

Text that I’m looking for the IF to match can be XYZ1, XYZ2, XYZ3 etc. There will always be an XYZ followed by 1-9 that I need it to pick up.
Currently I have it set to IF XYZ & cell reference (where I put in the 1-9 that it is), and the rest of my formulas work off that. I want to cut out the manually updating the cell reference each time I need my logic to populate.

Use case is in google sheets if that opens up more options.
Wouldn't REGEXEXTRACT do it? (I didn't really chap the exact use...maybe it is too late in the day...)

Back at this.

So the current function is FIND(REGEXEXTRACT( and looks through a bunch of variables separated by pipes ("XYZ[0-9]|ABC[0-9]" etc.), to return a number. The number should be around 22-25.

I just encountered where it picked up a match (and so number) as 4, but is also in the 22-25 range. So I want that if the number returned is under 15, then it should look for the second match and give me the 22-25 number.

Can't figure out the IF(FIND(REGEXEXTRACT= <15, search again and return nth match (in this case, 2nd).

Any ideas?

Offline jose34

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2016
  • Posts: 2395
  • Total likes: 154
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1333 on: December 17, 2019, 08:08:18 PM »
How do I use excel to calculate a bunch of names and return how many times a certain name is listed in the spreadsheet?

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12855
  • Total likes: 3302
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1334 on: December 17, 2019, 08:51:13 PM »
How do I use excel to calculate a bunch of names and return how many times a certain name is listed in the spreadsheet?
You would use the COUNTIF function. 
Code: [Select]
=COUNTIF(A:A,B1) if your name is in B1 and you are searching in column A.
Workflowy. You won't know what you're missing until you try it.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2623
  • Total likes: 52
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1335 on: December 17, 2019, 08:52:58 PM »
Get a list with each name once
Next to each name the formula is =COUNTIF(range with the names in the worksheet, cell with the name)
Make sure the range is locked.

For example, if the names you want to count are in C1:C100, and you have a list of names in column E, then F1 would be = COUNTIF($C$1:$C$100,E1)

Offline jose34

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2016
  • Posts: 2395
  • Total likes: 154
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1336 on: December 17, 2019, 09:10:37 PM »
not working, what about using the pivot table?

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2623
  • Total likes: 52
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1337 on: December 17, 2019, 09:11:23 PM »
not working, what about using the pivot table?
That would work also

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1785
  • Total likes: 157
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: USA
Re: Excel Help and Problems
« Reply #1338 on: December 18, 2019, 10:25:03 AM »
Get a list with each name once
Next to each name the formula is =COUNTIF(range with the names in the worksheet, cell with the name)
Make sure the range is locked.

For example, if the names you want to count are in C1:C100, and you have a list of names in column E, then F1 would be = COUNTIF($C$1:$C$100,E1)
To get a list of each name once in a large sheet, the fastest way is to use Google sheets' UNIQUE function.

Actually, office 365 had it as well?
https://exceljet.net/excel-functions/excel-unique-function

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12855
  • Total likes: 3302
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1339 on: December 18, 2019, 10:33:42 AM »
To get a list of each name once in a large sheet, the fastest way is to use Google sheets' UNIQUE function.

Actually, office 365 had it as well?
https://exceljet.net/excel-functions/excel-unique-function
I usually select the column and click Data > Remove Duplicates
Workflowy. You won't know what you're missing until you try it.