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

NoviceKB

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

  • Datasheet.JPG
    Datasheet.JPG
    119.7 KB · Views: 5

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
Assuming the header row has dates, as soon as you convert the data into a table, those dates will become text.
 
Upvote 0
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.
 
Upvote 0
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. :(
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top