3

I received this spreadsheet last year and thought it would be great to use year after year but it has an error in it. It is a spreadsheet that tracks vacation days, Personal holidays, Comp days, Sick days, etc. Column A Starting at row 9 going down is the months. Row 8 starting at column B going through AF are the number of days (not relevant to the formula but helps to keep track of what days it is tracking)

the formula reads like this (with may2 being a named range on the row that is giving me the #value) and FYI - Capital V means took the whole day as vacation and small v means it was a half day.

=sum(if(exact(may2,"V"),1,if(exact(may2,"v"),0.5,0)))

and the same response if I type it exactly as the row for April but insert the May named range. =sum(if(exact(may2,"V"),1,if(exact(may2,"v"),0.5)))

If I click on the row for April I see: {=sum(if(exact(apr1,"V"),1,if(exact(may2,"v"),0.5)))}

if I copy and paste it to the May row, I will get the results for obviously April since it uses the April named range. I am curious about the "{" and "}" that I see. I have never have seen these used before the = and at the end of the formula

I did think maybe there was something with the cells that are defined for the May row and have literally selected on each sell and hit delete. Still that same result

In addition if I test the working formula for another row in a separate cell - I did the same formula in another cell for April and it returned the same #value error.

I also thought maybe there was something in a hidden cell that is causing it so I made a new tab and typed everything in but still getting the same error.

Any insight on what it may be would be awesome.

Improve this question
1
  • These are array formulas
    – soandos
    Commented Jul 2, 2012 at 21:42

3 Answers 3

Reset to default
2

If you use SUMPRODUCT here you can avoid "array entry". Try this regular formula [revised]

=SUMPRODUCT(EXACT(may2,"v")/2+EXACT(may2,"V"))

Improve this answer
0
1

To learn more about array formulas, see here. What is happening is that your range is named incorrectly. To see how it could be done, see here.

Improve this answer
1
  • Ranges seemed to be named correctly as they work on other lines and I reviewed your link and this is how they were done. The formula provided below worked perfectly without changing how the range was named. Thank you for your answer.
    – Kim
    Commented Jul 3, 2012 at 14:43
0

The {} indicate that the formula is indeed a matrix operation. After copying the formula "confirm" it with CTRL+SHIFT+ENTER instead of ENTER only.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .