How not to skip a row in a formula

**tsakta13ole** · 06-07-2015, 06:05 PM

Hi. I'm attaching my workbook. My problem is a bit tricky and english is not my native language so sorry if I don't explain it correctly. In column E I have this formula in E4

Please Login or Register  to view this content.

That is ok for what I want. Also if I drag this, E5 will also be what I want (E4). But at E6 I want 'events exp up to 2 months"! in this formula to be R10 not R11. And for E8 R11, E10 R12 etc.

Thanks for any help!

RSX forum1.xlsm

**Richard Buttrey** · 06-07-2015, 06:22 PM

Hi,

Except that E4 doesn't contain the formula you show. It's missing the reference to the 'events exp' sheet.

What is it you're trying to achieve? If you can explain that and then manually add some typical results maybe we can come up with a simplified formula since it's by no means clear what you are doing with the current formula.

**tsakta13ole** · 06-07-2015, 06:39 PM

RSX forum1.xlsm

Sorry then. I attach the workbook again.

At E4 if 'events exp up to 2 months'!R9="-" I want the result to be rounddown(C4,0). If 'events exp up to 2 months'!R9="+" the result to be ceiling(c4,0). I already have done that. So C4 is 27.93, 'events exp up to 2 months'!R9="-" so rounddown(27.93,0) is 27. This is for even rows. At odd rows I just want the result of the previous row. So at E5 I want it to be 27. (I have already done that).

The problem with my formula is that at E6 it will try to see what 'events exp up to 2 months'!R11 equals because 'events exp up to 2 months'!R10 was at E5. So, how can I have:

E6 if(... 'events exp up to 2 months'!R10 ...
E8 if(... 'events exp up to 2 months'!R11 ...
E10 if(... 'events exp up to 2 months'!R12 ... etc. Odd rows will take the value of the previous even rows so I don't care if it tries to see what 'events exp up to 2 months'!R... equals.

Basically what I'm trying to do is for even rows, if the event is negative (eg. 'events exp up to 2 months'!R9="-") to rounddown C4 and i its positive to roundup.

Hope you understand what I'm trying to do.