How to Use an Excel Data Table for "What-if" Analysis

[AMiCIXELSYD]

Hi Danny, I just wonder if you can help me to see if there is another ways to set up my formula which I don't have to change monthly or yearly. I have an excel spreadsheet which keep track on of the sales for the pass couple years and I would like to set up the formula so at month end it will tell me how much sales for that month, and here is my formula

=COUNTIFS('Tracking'!$A:$A,">=­02/01/"&B20,' Tracking'!$A:$A,"<=02/28/"&B20­,'Tracking'!$L:$L,"L")

here is my formula

=c

[DannyRocksExcels]

@AMiCIXELSYD

My preference - in this case - is to use a Pivot Table to produce these results.

With a Pivot Table you would not have to change the formula, you would simply "point" your Pivot Table to the new data source each month.

The one thing your WOULD need to do is to GROUP the Date Field to produce Date, Month & Year Fields - This takes about 6 clicks of the mouse.

That is my suggestion.

Danny Rocks

The Company Rocks

[sarchnar9]

Hi Danny Rocks. i am currently student learning Excel, and I wounder if you could help me. i am looking for Formula and Function book. i dont know which book is the best and i am just a beginner. thank you, (God bless you)

[DannyRocksExcels]

@sarchnar9

I highly recommend the book "Excel 2007 Formulas" written by John Walkenbach. This is available for several versions of Excel. The Publisher is Wiley.

Good luck with your studies!

Danny Rocks

The Company Rocks

[abarashyasir1]

wawwww! thats really very informative and i got a lot of ideas in analysis.U did a goood job.

[DannyRocksExcels]

@abarashyasir1

Thank you for adding your comment. I am pleased that you picked up some good ideas from my tutorial.

Sincerely,

Danny Rocks

The Company Rocks

[abarashyasir1]

@DannyRocksExcels Dear could u please help me in defining the model that is relateed to risk and return.

[TrollUnderTheVolvo]

I should have watched this 7 hours ago, when I started working on this stupid assignment! 

[DannyRocksExcels]

@TrollUnderTheVolvo

Thanks! I DO hope that I was able to help you to complete your assignment.

Wehn I was learning how to use Excel, I almost always got confused by the explanations that I found in books on this topic. That is why I worked so hard to make my demonstration clear for my viewers to understand.

Danny Rocks

The Company Rocks

[dbskholicable]

thank you so much!



[DannyRocksExcels]

@dbskholicable

My pleasure. Thank you for writing to tell me that you enjoyed my video tutorial.

Danny Rocks

The Company Rocks

[gnossticc]

Thanks Danny, great explanation on how data tables ought to be used!

[DannyRocksExcels]

@gnossticc

Thank you for adding your comment. It is my pleasure to explain these concepts which, I admit, I had difficulty in understanding from traditional sources. As I like to say, "I work hard to make it easy for you to understand the conceprt that I present!"

Danny Rocks

The Company Rocks

[gnossticc]

@DannyRocksExcels Yeah, yeah, I also admit not fully understanding how to use Data Tables but your example cleared many doubts I've had.  Thanks again!

It would be interesting to measure how many man-work-hours can a spreadsheet save. Imagine doing a huge table by hand as it was done not too many years ago! Cheers!

[DannyRocksExcels]

@gnossticc

Thanks! I am pleased that my video tutorial helped you.

I don't even want to speculate how many man hours it would take to summarize what Excel can perform in 2 or 3 mouse clicks. The trick is, of course, to know which 2 or 3 mouse clicks to make! That is my mission - to demonstrate which mouse clicks to use to get the result that you are looking for.

Danny Rocks

The Company Rocks

[joseandres21fl]

yeah bad to read when it gets to the what if part

[DannyRocksExcels]

@joseandres21fl

Thanks for the clarification. For the past few months I have been producing my YouTube Videos in Hi Definition mode - makes them a lot easier to read.

Thank you for your feedback - very much appreciated!

Danny Rocks

The Company Rocks

[joseandres21fl]

bad resolution

[DannyRocksExcels]

@joseandres21fl

So that I can possibly help you, please elaborte.

Did you mean that the "Screen Resolution" was bad - hard to read,etc.? Or something else - di not help you, etc.?

Thanks,

Danny Rocks

The Company Rocks