Capacity Column Formula Is Not Calculating

Ummm... How can we change anything without seeing something...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I don't understand -- or it is so simple that I feel I am missing something.

The current formula in the "capacity" column (column U) is tank size-blank column (columnV-columnX). Because Excel treats blank cells as if they were the value 0, the formula in column U is correctly return the value from tank size (column V).

If this is not correct, what should the capacity column/column U be doing?

First observation: I was wrong earlier when I said that column X was empty. Column X only looks empty because you have white font on white background, and you have the "do not show zero values" option checked in options. I find that spreadsheets are more difficult to debug when I hide pertinent data. First thing I would suggest is to make sure that all pertinent data is readily visible to you, the programmer, so that you can see what is going on.

By making column X visible and making 0 values visible throughout the worksheet, I can see that X324 is returning 0, not 2140. Column X contains an OFFSET() function with some counting function. The logic in the counting functions inside of this offset function appears to be causing the OFFSET() function in X324 to return the 0 value in J324. (If you are unfamiliar with Excel's debugging tools, this can be seen using the Evaluate Formula tool on the Formula tab: https://support.office.com/en-us/art...6-A70AA409B8A7 ).

It appears to me that something is wrong with your logic here. In all the previous sections, the nonzero values in E:O of a given a row are to the left, and all the zero values are to the right. In the non-working section, this is no longer true. The logic in the OFFSET() function seems to be expecting that all zero values are in the leftmost columns and any zero values are in the rightmost columns.

It seems to me that something is different about how columns E:O are populated in the non-working rows, or the OFFSET() logic is incorrect for these rows. I cannot tell what the overall logic for these rows should be, so I am not sure what correction to suggest. I would suggest reviewing the formulas that populate E:O and make sure they are correct. If they are correct, then you need something different in your OFFSET() function.

Can you further clarify what should be going on here?