everydayExcel Newsletter Header

Claim 116% of Course Fees (For Singapore Companies only)

Great news! Now you can claim as much as 116% of the course fees through the Productivity and Innovation Credit (PIC) Program when you attend our courses.

The government has announced that companies, partnerships and sole-proprietors can now get back the money invested in training their employees through one or more of the following ways:

  1. Cash payout,
  2. Tax rebates (enhanced deductions),
  3. Cash Rebate

The PIC program will benefit ... more


Tracking Expenses

written by Zhang Da

I have recently graduated from school and started working. I always thought financial planning is important regardless what your income level is. Personally I love Excel so I have developed an Excel template to track my personal expense. I feel Excel is a perfect tool to do this. Here is how I designed my template:

I created ... more


Question and Answer

Question: Hi Jason, I have a pretty basic question but it has been bothering me so not sure if you have the solution. Sometimes I have a set of data where I need to compare between 2 columns or reference a column in a formula to derive a value. However when someone cut and paste or delete certain rows (e.g. shift cells down), the formula or comaparison still continue referencing the old cells and result in errors. How do I fix to reference the location so that even if any rows was deleted, it will reference the next row?

Answer: The INDIRECT formula convert a range that is presented in text format to a proper range. Because it is a text it will not move like a normal cell reference when the row is deleted. For example, if you want to refer to Cell D9, use =INDIRECT("D9")


Question: Column A has an Event ID and all the rows with the same ID appear together. What I would like is for in Column B the rows to be numbered consecutively where they appear in each Event ID. I have manually completed this so that you can see what I mean, but because there are thousands of these in reality I need a formula to do it automatically.

However, there is another twist! You will see that within each Event ID group the rows are sorted by the values in Column L (smallest to largest). Well, just occasionally 2 rows will have the same value in this column (see Col L, rows 15/16 and 23/24 and 42-45 which I have highlighted in yellow). Where this happens I need for the numbers in column B to be the same as each other and then for the subsequent numbers in the range to follow on normally.

Answer: For the normal numbering, you can use the if formula to compare the event id with the previous row. If they are the same, then the numbering continues, if not, restart the numbering from 1. So the formula is =IF(A2=A1,B1+1,1)

Now the next step is to deal with repeated values. The numbering should remain the same. To do this, you have to work on the true portion of the IF formula. The idea is to check for the value in column L to find out whether to add one more count or keep the previous count. So in this case, use

IF(L2=L1,B1,B1+1)

The above formula should replace the true portion of the 1st IF formula.

The completed formula is =IF(A2=A1,IF(L2=L1,B1,B1+1),1).

When you do that, you do not see any change in the numbering. That's because the value in column L is not exactly the same because of the decimals place not shown. In your example, there are more than 2 decimal place in the values. To solve this problem, you can use round on L1 and L2 so that the values are compared up to 2 decimal places only.

The revised formula is =IF(A2=A1,IF(ROUND(L2,2)=ROUND(L1,2),B1,B1+1),1).

Copy the formula to the rest of column B and you are done with the results

If you have any question regarding Excel, please feel free to send it to us.


Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.


Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.


Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"

 

Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.