Copying horizontal formulas using raw data that runs vertically in increments

M

mlarson

Well-known Member
Joined
Aug 25, 2011
Messages
509
Office Version
  1. 2010
Platform
  1. Windows
My challenge is trying to copy horizontally with the formula being dependent on raw data that is vertical. Any ideas on how to copy this formula? In the example below the formula for E5 is =SUM(B8+C7). The formula for F5 is =SUM(B9+C8), and so on. Any help? Thanks!

<table border="0" cellpadding="0" cellspacing="0" width="512"><col style="width: 48pt;" width="64" span="8"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td class="xl66" colspan="2" style="width: 96pt;" width="128">Formulas</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td class="xl63" align="right">6</td> <td class="xl65" align="right">6</td> <td class="xl64" align="right">8</td> <td colspan="2" style="">copy formula >>></td> </tr> <tr style="height: 15pt;" height="20"> <td colspan="2" class="xl67" style="height: 15pt;" height="20">Raw Data</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl63" align="right">3</td> <td class="xl68">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">3</td> <td class="xl65" align="right">5</td> <td class="xl68">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1</td> <td class="xl64" align="right">5</td> <td class="xl68">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" align="right" height="20">3</td> <td align="right">6</td> <td class="xl68">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">7</td> <td align="right">2</td> <td class="xl68">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">4</td> <td align="right">4</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">6</td> <td align="right">7</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">2</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Use the following in E5, then drag across.

Code: 
=INDIRECT("B" & COLUMN(H1))+INDIRECT("C" & COLUMN(G1))
 
Upvote 0
Thanks for your help on that! Here's another one I'm trying to figure out. It is similar to the previous post but a bit more complex.

Column A and B are given data. Columns C, D, E, F, and G need to use a formula to come up with the number shown.

When there is a 1 in B, count how many rows until there is a 1 in A. For example, the first row has a 1 for B and there are two more rows until there is a 1 for A. So, 2 is the answer. Since 2 corresponds with D, the 2 should be entered in D.

C=1 row difference
D=2 row difference
E=3 row difference
F=4 row difference
G=5 row difference

I'd also like to be able to copy and paste the formula beyond column G. Thank you very much for providing the formula that can be used to figure this out!

<table border="0" cellpadding="0" cellspacing="0" width="443"><col style="width: 48pt;" width="64" span="2"> <col style="width: 44pt;" width="59"> <col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl66" style="width: 48pt;" width="64">B</td> <td class="xl66" style="width: 44pt;" width="59">C</td> <td class="xl66" style="width: 48pt;" width="64">D</td> <td class="xl66" style="width: 48pt;" width="64">E</td> <td class="xl66" style="width: 48pt;" width="64">F</td> <td class="xl66" style="width: 48pt;" width="64">G</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">0</td> <td class="xl65" style="border-left: medium none;">1</td> <td class="xl65" style="border-left: medium none;">0</td> <td class="xl65" style="border-left: medium none;">2</td> <td class="xl65" style="border-left: medium none;">0</td> <td class="xl65" style="border-left: medium none;">4</td> <td class="xl65" style="border-left: medium none;">5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> </tbody></table>




<table border="0" cellpadding="0" cellspacing="0" width="498"><col style="width: 48pt;" width="64" span="2"> <col style="width: 86pt;" width="114"> <col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td class="xl65" style="width: 48pt;" width="64">
</td> <td class="xl66" style="width: 86pt;" align="right" width="114">
</td> <td class="xl66" style="width: 48pt;" align="right" width="64">
</td> <td class="xl66" style="width: 48pt;" align="right" width="64">
</td> <td class="xl66" style="width: 48pt;" align="right" width="64">
</td> <td class="xl66" style="width: 48pt;" align="right" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> <td class="xl67" style="border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>
 
Upvote 0
Enter in C1, then drag across/down.

Code: 
=IF($B1=1, IF(OFFSET($B1, COLUMN(A1), -1)=1, COLUMN(A1), 0), 0)
 
Upvote 0
Sorry on the duplicate post, wasn't sure if I should start a new thread or not since the question was kind of different. Thanks for your help on this! Mr. Excel is an awesome site!
 
Upvote 0
Since both INDIRECT and OFFSET are volatile functions they can negatively impact your sheet performance if used a lot on the sheet. If there are reasonable alternatives, I generally try to use the alternatives.

For your original problem, try this formula, copied across.

Excel Workbook
BCDEFGHI
5668136
6
73
835
915
1036
1172
1244
1367
142
15
Prob 1




For the second problem copy this formula across and down. I have assumed the formulas only need to go across to column G. If they do need to go further the formula will need a bit of a change.

Excel Workbook
ABCDEFG
10102045
20000000
31000000
40112340
51000000
61000000
71000000
81000000
90000000
100000000
110000000
121102040
130110300
141000000
150110000
161100000
170100005
180000000
190000000
200102000
210110000
221000000
Prob 2
 
Last edited:
Upvote 0
Peter, thank you for that information. On my spreadsheet they would actually need to go across 390 columns. Is the formula vastly different for that amount of data?
 
Upvote 0
mlarson said:
Peter, thank you for that information. On my spreadsheet they would actually need to go across 390 columns. Is the formula vastly different for that amount of data?
Click to expand...
You could very likely notice the issue with the OFFSET function then if you have copied that to 390 columns and presumably down at least 390 rows as well since that is over 152,000 OFFSET functions.

My C1 formula would just need to change to
=COLUMNS($C:C)*$B1*INDEX($A2:$A391,COLUMNS($C:C))

However, with so many columns I would consider this if it is possible for you to go this way. It will reduce considerably the amount of calculations your sheet needs to do.

1. Insert a new row 1.
2. Put a 1 in C1, a 2 in D1 and then drag that across your 390 columns to column OB so you have 1, 2, 3, ..., 390 across row 1.
3. Use this formula in C2 and copy across and down.

Excel Workbook
ABCDEFGHIJ
112345678
20102045670
30000000000
41000000000
50112340008
61000000000
71000000000
81000000000
91000000000
100000000000
110000000000
120000000000
131102040000
Prob 2
 
Upvote 0
Peter, wow, thank you! One last question if you would humor me. Is it possible to have in row C the number of rows it takes following a 1 in B to have a 1 in C. For example, a 1 in column B is followed by a 1 in column A two rows later, so the value is 2. And would this be possible for, say, 100,000 rows (but only the 3 columns)? Thanks so much!

<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl64" style="width: 48pt;" width="64">B</td> <td class="xl64" style="width: 48pt;" width="64">C</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">0</td> <td class="xl63" style="border-left: medium none;">1</td> <td class="xl63" style="border-left: medium none;">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">6</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">0</td> </tr> </tbody></table>
 
Upvote 0
Apologies, in the first sentence I meant "...in COLUMN C" not ROW.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
366
dilshod_k
D
Ramballah
  • Question Question
Transferring raw data into my own sheet
Replies
7
Views
281
Kerryx
K
A
  • Solved
STDEV.S/IF Formula Explanation
Replies
2
Views
317
Arts
A

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top