Formula Error/Mistake

Michaela Kamenska

Hi all,

I have a formula that autopopulates only certain cells. Now, it works fine in my other sheet, however, I had to do slight adjustments to it in this sheet and it clearly populates the correct cells, however, it is not giving me the correct value, and instead just puts 0.00. Can some please help me figure out the error in my formula?

=IF(AND(VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)) <= [Nov-20]$1, VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)) >= [Nov-20]$1), [Budgeted Hours]@row / COUNTIFS($[Nov-20]$1:$[Dec-22]$1, AND(@cell >= VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)), @cell <= VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)))))

Genevieve P.

Hi @Michaela Kamenska

I've tested your formula and it should work, however when I was dragging it across the row, this updated specific references... for example, [Nov-20]$1 updated to be [Dec-20]$1 when I drag it to the right. I presume this is by design, so you don't have to re-type in the column references per each column, is that correct?

If so, you'll need to add in an absolute reference ($) to your [Budgeted Hours]@row. Right now, if you drag this over from Nov to Dec, the formula will update so that instead of Budgeted Hours it will look to Duration (one column to the right). Then in the Jan column, this will update to look into the Nov-20 column, one more column to the right. Since that cell is blank, it will return a 0.

Add in $ before the column name, to make sure it's locked onto that Budgeted Hours column:

$[Budgeted Hours]@row

Full formula:

=IF(AND(VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)) <= [Nov-20]$1, VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)) >= [Nov-20]$1), $[Budgeted Hours]@row / COUNTIFS($[Nov-20]$1:$[Dec-22]$1, AND(@cell >= VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)), @cell <= VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)))))

Did that resolve your issue?? You can read more about drag-filling here, and absolute references here.

Cheers!

Genevieve

Genevieve P.

Hi @Michaela Kamenska

I've tested your formula and it should work, however when I was dragging it across the row, this updated specific references... for example, [Nov-20]$1 updated to be [Dec-20]$1 when I drag it to the right. I presume this is by design, so you don't have to re-type in the column references per each column, is that correct?

If so, you'll need to add in an absolute reference ($) to your [Budgeted Hours]@row. Right now, if you drag this over from Nov to Dec, the formula will update so that instead of Budgeted Hours it will look to Duration (one column to the right). Then in the Jan column, this will update to look into the Nov-20 column, one more column to the right. Since that cell is blank, it will return a 0.

Add in $ before the column name, to make sure it's locked onto that Budgeted Hours column:

$[Budgeted Hours]@row

Full formula:

=IF(AND(VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)) <= [Nov-20]$1, VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)) >= [Nov-20]$1), $[Budgeted Hours]@row / COUNTIFS($[Nov-20]$1:$[Dec-22]$1, AND(@cell >= VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)), @cell <= VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)))))

Did that resolve your issue?? You can read more about drag-filling here, and absolute references here.

Cheers!

Genevieve

Michaela Kamenska

Thanks Genevieve, it did the trick! :)

Genevieve P.

Wonderful!! Glad that was it!