Query re IF functions

[graemekoz]

Good afternoon everyone (or morning wherever you are in the world!)

I have a query about the best way to do a function on the spreadsheet which needs to refer to the contents of cells and if they are blank then to take it from another cell (and if it is blank, take it from another cell etc). My only thought was using IF functions but I can't get that to work.

What I am trying to do is as follows:

I am working out estimating attendances at event. I have used historical data to do this estimation so for example, 1 hour before the event, there is 20% of the crowd, therefore the estimated total attendance is x. The adjacent cell which we enter (say 100) would then enter in the EXPECTED ATTENDANCE a value of 500 (100/0.2). THen say at 30 minutes before the event, we may expect it to be 50% of the crowd in. When we get a figure for the 30 minute mark, it might be 400 and when we enter this value, the EXPECTED ATTENDANCE would be readjusted to 800 (400/0.5).

THerefore I need it to automatically calculate based on the LAST CELL where data is entered in a column.

Does that make sense? I hope so!! Can anyone help? Is there another way of doing that?

Thanks in advance.

Graeme

 

[Joe4]

There are a bunch of past questions which show you how to retrieve the value from the last populated cell in a column. Here is one: http://www.mrexcel.com/forum/showthread.php?t=551829

If you would like to see others, just use the Advanced Search functionality on this board.

 

[markmzz]

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">Time before event </td><td style="text-align: center;background-color: #C5D9F1;;">Crowd</td><td style="text-align: center;background-color: #C5D9F1;;">Estimated total attendance </td><td style="text-align: center;background-color: #C5D9F1;;">Expected attendance</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #C5D9F1;;">1:00</td><td style="text-align: center;background-color: #C5D9F1;;">20%</td><td style="text-align: center;background-color: #C5D9F1;;">100</td><td style="text-align: center;background-color: #C5D9F1;;">800</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #C5D9F1;;">0:30</td><td style="text-align: center;background-color: #C5D9F1;;">50%</td><td style="text-align: center;background-color: #C5D9F1;;">400</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #C5D9F1;;">0:20</td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #C5D9F1;;">0:10</td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #C5D9F1;;">0:05</td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: center;background-color: #C5D9F1;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=INDEX(<font color="Blue">C:C,MIN(<font color="Red">MATCH(<font color="Green">3E+300,C:C,1</font>),MATCH(<font color="Green">10,B:B,1</font>)</font>)</font>)/INDEX(<font color="Blue">B:B,MIN(<font color="Red">MATCH(<font color="Green">3E+300,C:C,1</font>),MATCH(<font color="Green">10,B:B,1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz