+ Reply to Thread
Results 1 to 17 of 17

2 variable data table not working

  1. #1
    Registered User
    Join Date
    08-28-2018
    Location
    Dhaka
    MS-Off Ver
    Office 2013
    Posts
    11

    2 variable data table not working

    I am trying to do a very simple valuation. I am trying to do a two variable data table using PE as one variable and number of shares as second variable. As I increase the Pre-IPO number of shares, EPS will be reduced. So, the share price should also reduce. But I am seeing an increase of share price as number of Pre-IPO shares are increased.
    Attached Files Attached Files
    Last edited by rashed125du; 08-28-2018 at 07:19 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: 2 variable data table not working

    Will you please attach the workbook here?

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    08-28-2018
    Location
    Dhaka
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: 2 variable data table not working

    I have attached the file. Thanks

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: 2 variable data table not working

    Where are the results you want to see, please? Not the incorrect ones.

  5. #5
    Registered User
    Join Date
    08-28-2018
    Location
    Dhaka
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: 2 variable data table not working

    I have done a one variable data table. Cell F3 to F8 should show exact values as shown in cell F13 to F18.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: 2 variable data table not working

    Thanks. So what would the manual calculation be? The TABLE function is not a native Excel function, so I don't know what it is supposed to be doing.

  7. #7
    Registered User
    Join Date
    08-28-2018
    Location
    Dhaka
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: 2 variable data table not working

    I have attached an updated excel sheet showing the manual calculation.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: 2 variable data table not working

    I'm sorry - I am having some sort of brain freeze here!

    Why are you unable to use that simple formula on your real datasheet? How did you set up TABLE? Is it a UDF? There is nothing in the workbook for use to troubleshoot as far as I can see in the absence of any VBA code.

  9. #9
    Registered User
    Join Date
    08-28-2018
    Location
    Dhaka
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: 2 variable data table not working

    I did the data table as I am used to doing it. I did not notice earlier that, it could have been done easier using formula. But the problem still exists. If this is an error, I will not use data table for any complex calculations too. In that case I will have the fear that, it may provide wrong result.

    Can you clarify UDF? I have not used any VBA code as I dont know coding.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: 2 variable data table not working

    I'm really sorry, but you haven't explained to me how you set up TABLE, and it isn't recognised here, so I can't troubleshoot it. I'm afraid I can't help you.

    UDF = user-defined function.

  11. #11
    Registered User
    Join Date
    08-28-2018
    Location
    Dhaka
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: 2 variable data table not working

    I have set up the data table in the usual way. Input row variables and column variables. Put the value that will change due to change of row and column variable at the top of the table. Then select area of the table>Data>What if analysis>Data Table>Select Row input cell>select column input cell>ok.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: 2 variable data table not working

    @Ali (and anyone else): for information - https://support.office.com/en-us/art...2-77542a5ea50b

    Calculate multiple results by using a data table

    Applies To: Excel for Office 365 Excel 2016 Excel 2013 Excel 2010 Excel 2007

    A data table is a range of cells in which you can change values in some in some of the cells and come up with different answers to a problem. A good example of a data table employs the PMT function with different loan amounts and interest rates to calculate the affordable amount on a home mortgage loan. Experimenting with different values to observe the corresponding variation in results is a common task in data analysis.
    Not something I use so I can't offer an answer.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: 2 variable data table not working

    Thanks, Trevor. Not something I use, either, so I am afraid I'm out, but I have put a call out to the wider community for help.

  14. #14
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: 2 variable data table not working

    I'd set it up like below.
    Using C2 as Row, C3 as Column
    0.JPG
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  15. #15
    Registered User
    Join Date
    08-28-2018
    Location
    Dhaka
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: 2 variable data table not working

    Thanks CK76. You have done the table correctly and the results you found match completely with my manual calculation using formulas.

    But problem is, as I try to do the same again in the earlier sheet that I have attached, that sheet again give me the wrong table. But when I redo this data table on your excel sheet, I am getting the correct table. Then I tried on another PC using a new sheet. I am getting the right result. What might be the source of the problem?

  16. #16
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: 2 variable data table not working

    It's due to F2:K2 range.

    You generated these using dependent formula on B7.

    Paste these back as values and you should get correct result.

    Edit: Or alternately use any cell other than B7 to hold the number.
    Last edited by CK76; 08-28-2018 at 03:20 PM.

  17. #17
    Registered User
    Join Date
    08-28-2018
    Location
    Dhaka
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: 2 variable data table not working

    Okay CK76. That solves the problem. I will never use dependent formula for scenario variables again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel table filter by date variable through data picker date variable
    By SamanH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2018, 01:09 PM
  2. Filter with a variable and select sheet with a variable not working
    By Jubb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-29-2017, 04:23 PM
  3. Replies: 1
    Last Post: 03-23-2017, 03:53 PM
  4. [SOLVED] Data Validation List to Return Variable/Dynamic Data based on Reference Table
    By dilbert1865 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2014, 09:08 AM
  5. [SOLVED] Partly working macro - variable fails if it holds certain data
    By SoulPrisoner in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-21-2013, 09:10 AM
  6. 3 Variable Data Table?
    By Ahouriani in forum Excel General
    Replies: 4
    Last Post: 02-24-2013, 06:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1