I have a table (see below):
<table width="422" border="0" cellpadding="0" cellspacing="0" height="68"><col style="width: 82pt;" width="109"> <col style="width: 67pt;" width="89" span="2"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 82pt;" width="109" height="17">
</td> <td class="xl25" style="width: 67pt;" width="89">MIN</td> <td class="xl25" style="width: 67pt;" width="89">MAX</td> <td class="xl25" style="width: 48pt;" width="64">PE ratio</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Project Size:</td> <td class="xl25">$0</td> <td class="xl25">$1,000,000</td> <td class="xl25">$250,000</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25">$1,000,001</td> <td class="xl25">$4,000,000</td> <td class="xl25">$500,000</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25">$4,000,001</td> <td class="xl25">$100,000,000</td> <td class="xl25">$750,000
</td> </tr> </tbody></table>
And I have Construction Projects. I bid the projects and I want Excel to calculate the PE - Project Equivalent.
For example, I have a project that is $3.5M. I see that $3.5M is between $1M and $4m so the PE Ratio is $500,000 (which means I need to return 1 PE for every $500,000 of the project's value). In this case the PE for the project would be $3.5M/$500,000=7.
Example 2, I have a project that is $800K. I see that $800K is between $0 and $1m so the PE Ratio is $250,000 (which means I need to return 1 PE for every $250,000 of the project's value). In this case the PE for the project would be $800,000/$250,000=3.2.
I need to be able to enter the value of the construction project and have Excel return the number of PE's (Project Equivalents) by looking at the table to see what PE Ratio to use, then dividing the project value by that number.
I'm thinking this is a VLOOKUP Function but I can't find an example of it. Any suggestions? Do you have a video that would help explain how to answer this question?
<table width="422" border="0" cellpadding="0" cellspacing="0" height="68"><col style="width: 82pt;" width="109"> <col style="width: 67pt;" width="89" span="2"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 82pt;" width="109" height="17">
</td> <td class="xl25" style="width: 67pt;" width="89">MIN</td> <td class="xl25" style="width: 67pt;" width="89">MAX</td> <td class="xl25" style="width: 48pt;" width="64">PE ratio</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Project Size:</td> <td class="xl25">$0</td> <td class="xl25">$1,000,000</td> <td class="xl25">$250,000</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25">$1,000,001</td> <td class="xl25">$4,000,000</td> <td class="xl25">$500,000</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25">$4,000,001</td> <td class="xl25">$100,000,000</td> <td class="xl25">$750,000
</td> </tr> </tbody></table>
And I have Construction Projects. I bid the projects and I want Excel to calculate the PE - Project Equivalent.
For example, I have a project that is $3.5M. I see that $3.5M is between $1M and $4m so the PE Ratio is $500,000 (which means I need to return 1 PE for every $500,000 of the project's value). In this case the PE for the project would be $3.5M/$500,000=7.
Example 2, I have a project that is $800K. I see that $800K is between $0 and $1m so the PE Ratio is $250,000 (which means I need to return 1 PE for every $250,000 of the project's value). In this case the PE for the project would be $800,000/$250,000=3.2.
I need to be able to enter the value of the construction project and have Excel return the number of PE's (Project Equivalents) by looking at the table to see what PE Ratio to use, then dividing the project value by that number.
I'm thinking this is a VLOOKUP Function but I can't find an example of it. Any suggestions? Do you have a video that would help explain how to answer this question?