Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Inserting formulas into the dashboard

0

I am trying to create a dashboard in excel and i am stuck with the formulas kindly assist

Answer
Discuss

Answers

0
Selected Answer

Break the task into five parts.

  1. Define the required calculation in plain language.
  2. Define the data sources.
  3. Encode the plain language in an Excel formula.
  4. Review the formula for re-use in other rows.
  5. Review the formula for re-use in other columns.

I shall take the formula in 'First Deliverable'!C4 as an example.

1. Define the required calculation in plain language.
Find all trucks by the following description:-
- Delivery Type must be = A4
- Warehouse must be = B4
- Arrival time must be before 2 PM
- Returning must be "Yes"

2. Define the data sources.
The data sources are columns on the 2. Data tab.
- Delivery type = '2. Data'!AG:AG (for faster calculation AG2:AG5000)
- Warehouse = '2. Data'!C:C (for faster calculation C2:C5000)
- Arrival time = '2. Data'!J:J (for faster calculation J2:J5000)
- Returning = '2. Data'!I:I (for faster calculation I2:I5000)
Each column has 1.4 million rows. By reducing the range to only 5000 rows Excel will be able to complete the searches faster.

3. Encode the plain language in an Excel formula.
Understand the difference between Function and Formula.
COUNTIF([Range], A4) is a function, saying "count how many times the value of A4 is found in [Range]". =COUNTIF([Range], A4)  is a formula consisting of a single function. The formula will write the result to the cell.
=COUNTIFS([Range1], A4, [Range2], B4) is a formula combining two functions. Test each function separately before you combine them, for example,  test =COUNTIF([Range1], A4) and =COUNTIF([Range2], B4). In 'First Deliverable'!C4 you have four functions as follows.

=COUNTIF('2. Data'!AG:AG,A4)
=COUNTIF('2. Data'!C:C,B4)
=COUNTIF('2. Data'!J:J,"< 2:00:00 PM")
=COUNTIF('2. Data'!I:I,"Yes")

As you see, I didn't reduce the size of the ranges to be searched but I do recommend that you do so. Having tested each of the four functions, combine them into one formula.

=COUNTIFS('2. Data'!AG:AG,A4,'2. Data'!C:C,B4,'2. Data'!J:J,"< 2:00:00 PM",'2. Data'!I:I,"Yes")

4. Review the formula for re-use in other rows.
Learn about absolute and relative addressing (Google these terms as related to Excel). The point is that your worksheet needs hundreds of formulas and you can save tons of time if you could just write one and copy it all over the place.
As you copy the above formula to other rows all row references will change. If you copy down 1 row the references will increase by 1. If you copy down by 10 rows the reference will change by 10. A4 will become A14, B4 will become B14. In this step you must decide whether you want that or not. So you look at each row number in the formula.
There are two such references, A4 and B4. In each case you actually do want the change. When you copy the formula from C4 to C5 you want the references to change to A5 and B5. Therefore you do nothing in this step, with this formula.
But if you should change the search ranges as I suggested, for example AG:AG became AG2:AG5000, you wouldn't want this range to turn into AG3:AG5001 because it would give you wrong results. Therefore these row numbers must be fixed. They must be made absolute. Precede them with a dollar sign, like AG$2:AG$5000

5. Review the formula for re-use in other columns.
Column references must be absolute if you want to copy formulas from one column to another. Your formulas in D4 and F4 are different but they all include the functions =COUNTIFS('2. Data'!AG:AG,A4,'2. Data'!C:C,B4). If you set them up now you will be able to copy the formulas and change only the last two functions in them. Therefore I recommend to reference all columns absolute. The rule to apply is, if you don't expressly want rows and columns to change when copying, make them absolute.
Now your revised formula looks like this:-

=COUNTIFS('2. Data'!$AG$2:AG$5000,$A4,'2. Data'!$C$2:$C$5000,$B4,'2. Data'!$J$2:$J$5000,"< 2:00:00 PM",'2. Data'!$I$2:$I$5000,"Yes")

Develop the formula for each cell using the same 5-step template. You will be allright. But let me point to the formulas in E4 and G4. Observe that it is always the result in the adjacent column (relative) which is devided by SUM(C4:D4) which is absolute. Therefore the existing formula [E4] =C4/SUM(C4:D4) can't be copied to G4. But properly revised for horizontal and vertical copying it could: =C4/SUM($C4:$D4)

However, that isn't all that could be improved. Look at row 31 where the same formula throws an error because there are no trucks. In fact, the function SUM($C31:$D31) returns zero. Therefore the formula in E4, which is later copied to E4:E15, G4:G15, E21:E32, G21:G32 and more, should accommodate this possibility. For that you use the IF() function. Embed the function you already have in an IF() function that surrounds it.

=IF(SUM($C4:$D4), C4/SUM($C4:$D4), 1)

Observe that any number challenged by the IF() function returns TRUE. Only zero returns FALSE. Therefore the function IF(SUM($C4:$D4), is equivalent to IF(SUM($C4:$D4)=TRUE, and you could expand my formula to include "=TRUE" to make it easier for you to read.
Now, if SUM(C4:D4) returns any value other than zero the above formula returns the calculated percentage as before, but if SUM($C4:$D4)=0 it will return 1 which the cell format displays as "100%". If you prefer to show zero, replace the 1. If you wish to show nothing, replace the 1 with "".

I hope this helps you with your home work. If it does, plese remember to select my answer. If you have a question which doesn't amount to "could you do my home work for me" please don't hesitate to ask. Good luck!

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login