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

NoviceKB

New Member
Joined
May 22, 2020
Messages
6
Office Version
365
Platform
Windows
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

Some videos you may like

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
Joined
Dec 30, 2008
Messages
9,085
Office Version
2019
Platform
Windows
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
Joined
Jun 12, 2014
Messages
38,814
Office Version
365
Platform
Windows
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
Joined
Dec 30, 2008
Messages
9,085
Office Version
2019
Platform
Windows
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
Joined
May 22, 2020
Messages
6
Office Version
365
Platform
Windows
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
Joined
May 22, 2020
Messages
6
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,837
Messages
5,446,791
Members
405,415
Latest member
Noodnutt

This Week's Hot Topics

Top