fbpx

All articles with 'sumproduct' Tag

Data and Calculations for our Customer Service Dashboard [Part 2 of 4]

Data and Calculations for our Customer Service Dashboard [Part 2 of 4]

Welcome back. In part 2 of Making a Customer Service Dashboard using Excel let us learn how the data & calculations for the dashboard are setup.

Designing Customer Service Dashboard
Data and Calculations for the Dashboard
Creating the dashboard in Excel
Adding Macros & Final touches

In this installment, we will examine all the variables, named ranges & various formulas that drive our dashboard. Also, you can download the full dashboard workbook and play with it to examine these formulas and learn better.

Continue »

14 ways to check if an year is leap year, using Excel [just for fun]

Published on Feb 29, 2012 in Excel Howtos, Learn Excel
14 ways to check if an year is leap year, using Excel [just for fun]

Today is February 29th, and that means, this year we have one more day to be awesome. So lets celebrate it in Excel style!

Lets learn 14 different ways to tell if an year is leap year, using Excel Formulas.

Why 14? because, we are awesome like that.

Continue »

Formula Forensics 012. – A Neat Formula

Published on Feb 16, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui

Chandoo.org;
Today we look at a very neat way of doing a complex Nested If or Vlookup style problem with a simple but beautiful Sumproduct based formula.

Continue »

Formula Forensics 011. Lykes Formula

Published on Feb 9, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 011. Lykes Formula

Lykes asked Formula Forensics “How does a formula work?” Lykes has been using the formula without understanding it.
The formula counts how many times the letters from a list of letters occur in a list of words.
Today we look at a Lykes question.

Continue »

Comparing 2 Lists with a Twist

Published on Feb 6, 2012 in Excel Howtos, Learn Excel
Comparing 2 Lists with a Twist

We love to compare. The instinct to compare leaves no one. Even my two year old twins compare their toys with each other (and fight).

It would make Excel hugely popular if Microsoft builds a handy data comparison tool right in to it. Alas, they have customizable ribbon, 3d effects & equation editor…

Since comparison is one of the main uses of Excel, we have written extensively about it here.

But there is always one more interesting comparison problem. Today, I want to share one such problem, based on a comment left by N-Man.

Continue »

Formula Forensics No. 010 Count How Many Times a List of Values Occurs in a Range

Published on Feb 1, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 010 Count How Many Times a List of Values Occurs in a Range

Count How Many Times a List of Values Occurs in a Range
(or How Can I Simplify My Formula)

Today in Formula Forensics we look at how to count how many times a range of values occurs within a Range of cells and in the process simplify a very nasty formula.

Continue »

Formula Forensics. No 007 – Sumproduct

Published on Dec 21, 2011 in Formula Forensics, Huis, Learn Excel, Posts by Hui
Formula Forensics. No 007 – Sumproduct

One of the most asked questions within the posts and Forums at Chandoo.org is, How Does Sumproduct work ?

Today in Formula Forensics we take at look at just that with a few worked examples.

Continue »

Formula Forensics 006. Palindromes

Published on Dec 15, 2011 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 006. Palindromes

Palindrome: – A palindrome is a word, phrase, number, or other sequence of units that can be read the same way in either direction, with general allowances for adjustments to punctuation and word dividers (“Wikipedia”).

This week in Formula Forensics we’re having a look at determining if a cell contains a palindrome.

Continue »

Formula Forensics No.004 – Fred’s Problem

Published on Nov 30, 2011 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics No.004 – Fred’s Problem

.
.
.
.
.
.
.

This is the Forth post in Chandoo’s, Formula Forensics series.

Last week Luke showed us how to extract a sorted list according to a criteria from a larger list
and he analysed a formula to solve this problem

This week we look at Fred’s Problem…

How do I simplify a very long formula?

Continue »

Christmas Gift Shopping List Template – Set budget, track your gifts using Excel

Published on Nov 23, 2011 in excel apps, Learn Excel
Christmas Gift Shopping List Template – Set budget, track your gifts using Excel

Last year, Steven shared a beautiful Christmas Gift List template with all of us. It is packed with lots of Excel goodness. Just a few days ago, he emailed me another copy of his file with some improvements. So if you are planning for Christmas shopping and want a handy tracker, you don’t want to miss this.

Continue »

Sum of Values Between 2 Dates [Excel Formulas]

Published on Sep 27, 2011 in Excel Howtos, Learn Excel
Sum of Values Between 2 Dates [Excel Formulas]

Lets just say, you run a nice little orange shop called, “Joe’s Awesome Oranges“. And being an Excel buff, you record the daily sales in to a workbook, in this format. After recording the sales for a couple of months, you got a refreshing idea, why not analyze the sales between any given 2 dates? […]

Continue »

Check if a Word or Phrase is Palindrome using Excel Formulas [Weekend Fun]

Published on Aug 12, 2011 in Excel Howtos, Learn Excel
Check if a Word or Phrase is Palindrome using Excel Formulas [Weekend Fun]

The other day, while I was putting my kids to sleep, this idea came to me. How do I check if a cell contains a palindrome, using Excel formulas?

Next morning, I wrestled with excel for about 20 minutes and boom, the formula is ready.

But what is a palindrome?
A palindrome is a word, phrase, verse, or sentence that reads the same backward or forward. For example: A man, a plan, a canal, Panama!
So, to check if a cell contains palindrome, we need to reverse the cell contents and see if both original and reverse are the same.

Continue »

Get busy this weekend, with OR XOR AND [Excel Homework]

Published on Jul 29, 2011 in Excel Challenges, Excel Howtos
Get busy this weekend, with OR XOR AND [Excel Homework]

Some of you have heard my neighbor’s dog bark in a video or two that I recorded. While I was busy explaining how to do something awesome in Excel, this dog would decide to bark, adding her own two cents to the lesson. Quite a few of my VBA class students have grown used to it. So much that they complain when a lesson doesn’t have a couple of woofs. But I digress.

So coming back, one of the dogs (probably stray) has decided that she should bring her infant puppies and hide them under our terrace stair case. So, now we have 2 cute little puppies barking day long (and very late in to the night) just outside my office window. We have tried hard to get rid of them, but they somehow sneak back in and start barking or crying. So, I will be busy this weekend trying to move them out.

But that doesn’t mean, you have to live Excel-less for a few days. So I have a homework.

OR XOR AND, Get busy this weekend!

Don’t worry. I am not speaking elvish or something. OR, XOR & AND stand for bit-wise operations. This week, your task is to write formulas in Excel that would get the bit-wise results for AND, OR & XOR.

Continue »

Sumproduct function to Consolidate Revenues?

Published on Jul 12, 2011 in Excel Howtos, Financial Modeling

Chandoo.org is all about simplifying life using Excel. When I first started visiting the site, I was amazed at the amount of information on the site, which made your life easier. My next few posts would be about excel functions which can make your life for Financial Modeling easier! These are simple examples that you […]

Continue »

Comparing Lists of Values in Excel using Array Formulas

Published on Jun 14, 2011 in Excel Howtos, Learn Excel
Comparing Lists of Values in Excel using Array Formulas

Last week, we had a home work on Calculating Donation Summaries using Excel Formulas. This is a good case where array formulas can help us. So today, we will learn how we can use Array Formulas to compare lists of values and calculate summaries. Towards the end of this post, you can see a video that explains the solution to Donation Summary Calculation problem.

Continue »