# Thread: Earned Value Calculation (Excel XP)

1. ## Earned Value Calculation (Excel XP)

I'm not sure I'm even going to explain what I need this time. The best I can hope for is that you'll be able to figure it out from the two sample sheets I'm including. I'm creating an earned value template for my company (called "Mine.xlt" in the zip file), using the system illustrated in a sample we have (called "Theirs.xls" in the zip file). LATER: I just noticed that the "Projected Cumulative" totals I have in the "Historical" sheet are off (I merged cells and screwed up the formulas). The values on the "S-Table" sheet are correct.

I have everything working except one last calculation, the number of days ahead or behind schedule, which I have highlighted in red on the "Historical" sheet in both workbooks. What I'm hoping to do is figure out a way to calculate this value without the large VLOOKUP table they use in the sample. Basically, the workbook calculates a dollar amount per day per month based on the amount budgeted for that month and the total number of days in the month. Then, it looks at how much money has been "earned" to date and cross-checks that number to the date when the project should have hit that dollar amount.

My set-up is a bit different than theirs. I combined what they are calling the EV-Table with the Historical sheet, but that's a minor modification. The major difference is that I'm using Networkdays instead of calendar days (so even though I'm using pretty much the same dates and values as the sample, the amounts aren't exactly the same, although the totals are).

I unhid the "Dates" sheet that holds the VLOOKUP table, but I didn't unhide the rows that they've used to calculate the final value (I wasn't sure if seeing the "final" version of the "Historical" sheet would help in understanding what was going on, so I left them hidden). Nothing is protected.

Thanks for any help you can offer. I'm really lost on this one. Nothing new, but I was hoping for more clarity by now!

--Karyl

2. ## Re: Earned Value Calculation (Excel XP)

In Dates!D55 enter 0
In dates!D57:
=D56+C57
Copy D57 to E57:P57 (or whatever)

In Historical!C15:
=IF(C9=0,0,INT(TREND(OFFSET(Dates!\$E\$55,0,MATCH(C9 ,Dates!\$D\$57:\$P\$57)-2,1,2),OFFSET(Dates!\$E\$57,0,MATCH(C9,Dates!\$D\$57:\$ P\$57)-2,1,2),C9)))Copy C15 to D15:N15

In C16 enter:
=IF(C9=0,"",Dates!\$E\$51+C15)

Copy C16 to D16:N16

Steve

3. ## Re: Earned Value Calculation (Excel XP)

Thanks, Steve. I made the modifications to the "Theirs" workbook per your instructions, and it works great. Of course, as usual, I have no idea WHY it works, but I printed out the information on the TREND function, and I'll work at making sense of it when I've got some concentrated time to devote to it, probably over the weekend. Then I'm going to need to adapt it to work with the "Mine" workbook which uses Networkdays instead of calendar days. I'm assuming that the basic approach will still work, however. Please advise if you know now that I'm going to run into trouble. Many thanks!

--Karyl

4. ## Re: Earned Value Calculation (Excel XP)

I didn't look too heavy into the details of the spreadsheets and concentrated on eliminating the need for the lookup.

The technique is general. It is just interpolating between two points (the INT is in there to match your numbers, I calc 10.3 you got 10 from the lookup, for example).

The MATCH gets the starting point for the data, the offset is used to extract the 2 "days of month" and the 2 "total costs".

If you need more details about how it works or adapting it to other situations, let me know.
Steve

5. ## Re: Earned Value Calculation (Excel XP)

Okay, I gave this a shot. I can understand the theory of what you're doing, and generally get the process of how you're doing it here, but I have very little hope that I'll be able to modify it to work on my version which is using Networkdays instead of calendar days and does not, right now, have any type of "Dates" sheet for reference as the original does.

I was able to figure out how to calculate the dollars per month based on total workdays without reference to another sheet (with a lot of help from here), and I was hoping to be able to do the same with days ahead and behind schedule, but if that isn't possible, I guess adding another sheet would work. But I'm stumped on how to do that, too. My template has to work for ANY date range the user sets up. The sample I have is locked to a 12-month span. They provide another template for 24 months, but I don't want multiple templates; I want one that will work for any range of dates up to three years (on this one) and eventually five years (when we move to Office 2003).

So, any help anyone has time to offer during the next week will be greatly appreciated. I'll be on vacation starting Wednesday and won't get back to this at work until next Tuesday. But I will be checking e-mail at home if you have questions or want me to give something a try. Many thanks!

--Karyl

6. ## Re: Earned Value Calculation (Excel XP)

I don't see the "similarity" between the 2 spreadsheets or how you want to calculate "days behind/ahead of schedule"

Given your example workbook, on the "historical" Sheet, in the cells J10, L10, N10, P10, and R10, what values do you calculate and what is the logic for these 5 calcs?

If I understood that I might be able to come up with a formula or a user function. Without some idea of your logic it is difficult to come up with a formula.

If your calcs are like in the "theirs" workbook (the daily budget and monthly budget is not "consistent") then you will either have to use the intermediate calcs and interpolations (as I gave you earlier) or you will have to figure out a "model" to derive the values (since I don't completely understand, I did not try to make a "predictive model").

Steve

7. ## Re: Earned Value Calculation (Excel XP)

Thanks for taking another look. I

8. ## Re: Earned Value Calculation (Excel XP)

Try this, I am still not sure I understand, but here is a go.
In Sheet S-Table:
Move I5:I6 to H5:H6
In I4 & I5 enter: 0
In J4 enter:
=NETWORKDAYS(StartDate,MIN(EndDate,DATE(YEAR(J7),M ONTH(J7)+1,0)),Holidays)
Copy J4 to J5:U4
These are the newworksays/month

In Sheet Historical
In J10 enter:
=IF(J6=0,0,INT(TREND(OFFSET('S-Table'!\$I\$4,0,MATCH(J6,'S-Table'!\$I\$5:\$U\$5)-1,1,2),OFFSET('S-Table'!\$I\$5,0,MATCH(J6,'S-Table'!\$I\$5:\$U\$5)-1,1,2),J6)))-'S-Table'!J4
In L10:
=IF(L6=0,0,INT(TREND(OFFSET('S-Table'!\$I\$4,0,MATCH(L6,'S-Table'!\$I\$5:\$U\$5)-1,1,2),OFFSET('S-Table'!\$I\$5,0,MATCH(L6,'S-Table'!\$I\$5:\$U\$5)-1,1,2),L6)))-'S-Table'!K4
In N10
=IF(N6=0,0,INT(TREND(OFFSET('S-Table'!\$I\$4,0,MATCH(N6,'S-Table'!\$I\$5:\$U\$5)-1,1,2),OFFSET('S-Table'!\$I\$5,0,MATCH(N6,'S-Table'!\$I\$5:\$U\$5)-1,1,2),N6)))-'S-Table'!L4
Etc
Note you can copy J10 to L10 and to N10, but since you have a merged cell you will have to edit the final reference to only go up by 1 column.

Steve

9. ## Re: Earned Value Calculation (Excel XP)

Thank you for your efforts on my behalf. I was able to get this working this afternoon, although the results I got on the sample workbook I sent seemed screwy. But in the real workbook, the values calculated for days ahead/behind seemed reasonable when compared to the original. By the time I was done, I was beyond being able to calculate it manually this afternoon, however, so I passed it on to my boss, who lives and breathes earned value. He'll check it manually to see if he comes up with the same breakdown for days ahead and behind schedule as the TREND function. I'll let you know.

Again, thanks!

--Karyl

10. ## Re: Earned Value Calculation (Excel XP)

Steve wrote, "If you need more details about how it works or adapting it to other situations, let me know."

Okay, Steve, I've adapted your formula to my real spreadsheet, and it is working perfectly. That is totally amazing to me and has astonished many who have looked at it! It is really cool. The problem now: I don't have a clue how it works! My customer is a bit worried that once we release the template, and folks start using it, our lack of knowledge will make it impossible for us to either answer questions or troubleshoot problems. So, if you can explain this in terms that an Excel novice with a VERY limited math background (like, nothing higher than maybe 5th grade) could understand, I'd greatly appreciate it. Many thanks!

Here's how it looks in my final version:

StartDays and StartCumulative are both 0. ProjCumulative are the cumulative dollars projected to be earned each month. Row 8 is the actual earned value for that month based on the percent complete. I'm not sure what the Offset is getting. I THINK the cumulative Networkdays. The result of the formula is the number of days ahead or behind schedule.

IF(R8C="","",INT(TREND(OFFSET(StartDays,0,MATCH(R8 C,ProjCumulative)-1,1,2),OFFSET(StartCumulative,0,MATCH(R8C,ProjCumu lative)-1,1,2),R8C))-R1C)

Please let me know if you need the sheets to go with it. However, the original sheets I attached to my message are still valid in this context. I just named some ranges, I think.

--Karyl

11. ## Re: Earned Value Calculation (Excel XP)

You don't say what the named ranges refer to or where you put the formula. Also since there were several changes to be made to the sheet, it might be better with a new attached file so we can see what you are doing.

I will try to explain, based on what you have:

OFFSET is used to create "range" which can be used in formulas just like a range you name or use with references:
MATCH is used to "find" an item in a range/list and give its relative position
TREND is used to calculate a linear trend. I use it for linear interpolation. (To be honest, I am not sure linear trend is a 5th level skill).
INT takes the Integer part of number (it is the number without the decimal values)

More on these can be obtained from the HELP.
We can "break apart" the formula you give:
IF(R8C="","",INT(TREND(OFFSET(StartDays,0,MATCH(R8 C,ProjCumulative)-1,1,2),OFFSET(StartCumulative,0,MATCH(R8C,ProjCumu lative)-1,1,2),R8C))-R1C)

MATCH(R8C,ProjCumulative)
This finds the relative placement of the lookup value in the list. It looks up the contents of the cell in row8 of the current column in the range named by "ProjCumulative". It is not an exact match. It will find the "relative item number" for the value <= to the listed value. If (eg) your list is 0,5,10, 25 etc, and the value in row8 of the column is anynumber greater than or equal (GTE) to zero but less than (LT) 5 it will give a 1. It will give 2 if GTE 5 but LT 10. It will give 3 if GTE 10 but LT 25, etc)

MATCH(R8C,ProjCumulative)-1
gets 1 less than the relative number. I do this since I use offset and the current place is zero not 1.

OFFSET(StartDays,0,MATCH(R8C,ProjCumulative)-1,1,2)
Offset defines a range.
It starts in the cell defined by the range "StartDays". It goes 0 rows down (ie does not change rows) and moves over MATCH(R8C,ProjCumulative)-1 cols to the right. The range is 1 row tall and 2 cols wide.

This range is 2 values. It should consist of the Networkdays range consisting of the "ProjCum" value that is looked up: that is, the network days should be between these 2 values

OFFSET(StartCumulative,0,MATCH(R8C,ProjCumulative)-1,1,2)
This does the same thing, getting 2 values that the "projCumulative" should be between.

TREND(OFFSET(StartDays,0,MATCH(R8C,ProjCumulative)-1,1,2),OFFSET(StartCumulative,0,MATCH(R8C,ProjCumu lative)-1,1,2),R8C)
This calculates the "trend" from the 4 days. If your value in row 8 of the column is exactly half-way between the range of ProjCum values, then the value will be the number exactly half-way between the networkdays range. Whatever part of the range the ProjCum, it calculates that proportion for the networkdays.

INT(TREND(OFFSET(StartDays,0,MATCH(R8C,ProjCumulat ive)-1,1,2),OFFSET(StartCumulative,0,MATCH(R8C,ProjCumu lative)-1,1,2),R8C))
This takes the Integer portion of the days (only whole number days, no fractions)

INT(TREND(OFFSET(StartDays,0,MATCH(R8C,ProjCumulat ive)-1,1,2),OFFSET(StartCumulative,0,MATCH(R8C,ProjCumu lative)-1,1,2),R8C))-R1C
This subtracts the value from row 1 of the column from the number of days calculated. I assume row 1 has the starting days and you want the days past the start.

IF(R8C="","",INT(TREND(OFFSET(StartDays,0,MATCH(R8 C,ProjCumulative)-1,1,2),OFFSET(StartCumulative,0,MATCH(R8C,ProjCumu lative)-1,1,2),R8C))-R1C)
The IF part looks at the value in row8 of the column. If it is null or blank, it displays a null,
If it contains something it will calculate as described above.

Hope this helps,
Steve

#### Posting Permissions

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