#valve Error

[justlearning2003]

Hey folks,

I am getting the #Valve error message in one of my formula's and after doing some research, I cannot determine where it is coming from.
The formula starts of with the "=--if(" so I as some people sugguested, I tired to replace the -- with Sum but never had any luck. The formula is
relatively straight forward, it is referencing other cells within it. The file is attached.

=--IF(J14="","",IF(J14<=G14,$W$12,"")&IF(T14=4,$X$12,"")&IF(T14=3,$Y$12,"")&IF(T14=2,$Z$12,"")&IF(T14=1,$AA$12,"")&IF(T14=0,$AB$12,"")&IF(U14=4,$AC$12,"")&IF(U14=3,$AD$12,"")&IF(U14=2,$AE$12,"")&IF(U14=1,$AF$12,""))


Thank you

 

[SkipVought]

hi,

you are combining the use of ARITHMETIC operators (--) and STRING operators (&). #VALUE! will happen if any of the referenced cells contain string values.

What is your objective. Please explain in plain prose without any formula or code talk.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[justlearning2003]

Ok here goes.
The user will enter a value in columns C and J. Based on those values, I do a few formula's on the side to calc working days between those two dates. Based on the result I display in column P. For example, enter Mar 7/2011 in C14 and Mar 14/2011 in J14. So this sheet simple calc`s scores of different line items. Each item is worth a percentage, say 2.5% for line item 14. So based on the certain dates we hit we can earn different amounts of that 2.5%. Make sense?

 

[mscallisto]

Maybe you can try to debug the formula yourself.

Here is one debug technique that lets you look at individual or multiple formula component values:

http://chandoo.org/wp/2008/12/15/formula-debugging-tips-excel/

Sam

 

[GlennUK]

Ok here goes.
The user will enter a value in columns C and J. Based on those values, I do a few formula's on the side to calc working days between those two dates. Based on the result I display in column P. For example, enter Mar 7/2011 in C14 and Mar 14/2011 in J14. So this sheet simple calc`s scores of different line items. Each item is worth a percentage, say 2.5% for line item 14. So based on the certain dates we hit we can earn different amounts of that 2.5%. Make sense?

You don't seem to have covered what the formula is supposed to be doing ... it looks like it's concatenating a load of cells optionally ... what's in the cells, and what are you expecting as a result?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[justlearning2003]

Hey folks, I think is it better for me to fully explain what I am looking to accomplish. We have a bonus system at work that looks at dates and I need to count the number of business days(Minus any holidays) between those dates. Based on the difference, we will receive a scored percentage for how good, bad or in the middle we do. Each line item is worth a different value, for this example I will use 2.5%. For example, if we commit to the client we will provide a report investigation by say June 10th. The June 10th is the expected date and we will receive 50% of the value of the line item, in this case 1.25%. To receive 100% we need to have the report submitted 5 business days earlier, June 3rd , as seen on the file as excellent date, resulting in 2.5%. But if we say send them the report by June 9th we get 60% of the value. Below is an example of the scale used for a line item worth 2.5% and the item has a scale of plus/minus 5 days. I use the Networkdays to calc out difference between business days. If anyone else has another approach or input on how to make this more efficient that would be great.

1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1
2.5% 2.25% 2.00% 1.75% 1.50% 1.25% 1.00% 0.75% 0.50% 0.25%

 

[SkipVought]

[tt]
=INDEX(2:2,1,MATCH(A3,1:1,0))
[/tt]
where row 1 & 2 is your data.
A3 has .5

returns .0125

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}