Function Arguments window shows result, cell shows a 0

**fluffsmckenzie** · 01-09-2013, 03:07 PM

I've entered the following formula into my worksheet:

Please Login or Register  to view this content.

In the Function Arguments window it gives me the correct result of Blech, however in the actual cell it's giving me a 0. Before you respond let's go down the list of things I've tried:

-The CTRL+` method
-The replace all = sign method
-The copy paste special add (and multiply) method
-The close and open method
-The copy pasta

I don't know what else to do or how to fix it, I'm sure I tried more (or at least felt like it) but I'm starting to forget what else I tried. If anyone has any ideas please let me know.

**FDibbins** · 01-09-2013, 03:22 PM

hi fluffs and welcome to the forum

In the Function Arguments window it gives me the correct result of Blech, however in the actual cell it's giving me a 0

what do you meant by "the Function Arguments window it gives me the correct result of Blech"?
if you F2 and then F9 in the cell, it will "calculate it for you and show you the result - did you try that yet (its not in your list)

**fluffsmckenzie** · 01-09-2013, 04:14 PM

I did not try the F2 F9 method. It shows me the correct result, but then it only pastes the value (I'm sure you knew this), is there a way to keep it "dynamic"?

**FDibbins** · 01-09-2013, 04:30 PM

no, once its converted, it stays that way, unless you hit escape instead of enter.

perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**fluffsmckenzie** · 01-09-2013, 04:59 PM

Here's the sheet. On the worksheet titled "Sheet5" in Column I Row 891-893 should be the perfect cross-section of what I'm trying to achieve. Thank you so much for your help.

Tax Formulas-Public.xlsx

**FDibbins** · 01-09-2013, 05:17 PM

you have a different formula in I291-293 to the rest of them, and its causing a circular error circ error can cause strange things to happen in your workbook, and are best avoided.

Also, you are using 2010, you have access to a function called iferror(). I would suggest that you use it to remove the #NA errors, and then simplify your formulas using if(cell-ref="",

another suggestion, looking at that formula in I...the only thing that changes from 1 row to the next is a cell ref at the start, and another 1 at the end. to shorten/simplify the formula, why dount you put all the rest of that text (combined) in a cell some place and then just reference it?
so....
=IF(ISNA(B890),"",CONCATENATE("i:",J890,";a:4:{s:5:","""","#name","""",";s:39:","""","uc_order_condition_delivery_postal_code","""",";s:6:","""","#title","""",";s:5:","""",D890,"""",";s:13:","""","#argument_map","""",";a:1:{s:5:","""","order","""",";s:5:","""","order","""",";}s:9:","""","#settings","""",";a:2:{s:6:","""","negate","""",";i:0;s:7:","""","pattern","""",";s:5:","""",D890,"""",";}}"))
would become...
=IF(ISNA(B890),"","i:"&J890,"&X1&D890&""",}}")
where X1 contains...
;a:4:{s:5:"#name";s:39:"uc_order_condition_delivery_postal_code";s:6:"#title";s:5:"94135";s:13:"#argument_map";a:1:{s:5:"order";s:5:"order";}s:9:"#settings";a:2:{s:6:"negate";i:0;s:7:"pattern";s:5:"

**fluffsmckenzie** · 01-09-2013, 05:48 PM

I did simplify the Formula by separating out the CONCATENATEs to other columns. My mind went to file size when thinking about doing that but it is a much easier way to figure out the formula.

The formulas are different there because that's the best cross section to figure out what the new formula is supposed to be. Basically I want to the cell to identify that the last option part was right above it while the next cell is supposed to confirm that there is indeed no more data to process. It's only when the cell after the final one containing data in the column is there a "circular error".

Either way I changed the way the formula works. Instead of working up and down I checked another column that verified it was the end of it. Thank you.