# My formula results changes completely after I convert my worksheet into a table.

#### NoviceKB

##### New Member
Hi everyone,

I have finally figured out why my excel formula was acting up. Each time I convert the range into a table it affects the results of my cells which have formulas.

Below is the formula you can insert in cell L5 in the screenshot:

=IF(OR(\$D5>EOMONTH(L\$4,0),AND(\$E5<L\$4,\$E5>0)),0,((\$F5/\$G5)/DAY(EOMONTH(L\$4,0)))*(IF(AND(\$H5>0,\$I5>0),((MAX(0,MIN(\$H5-1,EOMONTH(L\$4,0))-MAX(\$D5,L\$4)+1)+(MAX(0,IF(\$E5>0,MIN(\$E5,EOMONTH(L\$4,0)),EOMONTH(L\$4,0))-MAX(L\$4,\$I5)+1)))),(MAX(0,IF(\$E5>0,MIN(\$E5,EOMONTH(L\$4,0)),EOMONTH(L\$4,0))-MAX(L\$4,\$D5)+1)))))

Please take note that the screenshot is of the work BEFORE being converted to table. As soon as I convert it to a table 'all hell break loose' in the green coloured columns which have the formulas.

THanks and any suggestions will be gratefully accepted.

#### Attachments

• 119.7 KB Views: 4

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### jasonb75

##### Well-known Member
Below is the formula you can insert in cell L5 in the screenshot:
It is not possible to insert a formula into a screen capture, however formulas do work well with XL2BB captures.

Please repost your sample using Xl2BB by following the instructions in the link above or convert the the sheet to a table as you have done previously, then post the table formula from the same cell.

Sometimes a mix of absolute and relative ranges can be problematic in a table formula, but without both versions of the formula it would mean a lot of guesswork.

#### Fluff

##### MrExcel MVP, Moderator
Assuming the header row has dates, as soon as you convert the data into a table, those dates will become text.

#### jasonb75

##### Well-known Member
Not a problem that I've encountered before, Fluff, but don't think I've ever used dates as headers in a table.
If that is the cause of the problem then changing each instance of L\$4 to L\$4+0 should fix it.

#### NoviceKB

##### New Member
Assuming the header row has dates, as soon as you convert the data into a table, those dates will become text.
Yes Fluff. They do have dates in the header. Converting them back to dates didnt help anyway.

#### NoviceKB

##### New Member
Not a problem that I've encountered before, Fluff, but don't think I've ever used dates as headers in a table.
If that is the cause of the problem then changing each instance of L\$4 to L\$4+0 should fix it.
You are a genius!!! It worked!!!!!!!!!!!!!!!!!!!!!!!!!! You just made my weekend!!!! THanks a lot to all contributors! really appreciate the fast response.