Formula Showing #Value

[ianharper68]

Hi All ....

I have the following formula and it works fine except in blank cells can anybody help please:

=IF(Q8=0,"-",INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8)))*[@[Chargeable Days]]

 

[Alex Blakenburg]

I am guessing a little here but see if this works in AH8:

=IF(Q8=0,0,
IF(T8="NO",
INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8)),
INDEX(Calculations!$C$36:$E$36,XMATCH(Q8,Calculations!$C$35:$E$35,-1))*(IF(Q8<2,1,Q8))*N([@[Chargeable Days]])))

 

[ianharper68]

I am guessing a little here but see if this works in AH8:

=IF(Q8=0,0,
IF(T8="NO",
INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8)),
INDEX(Calculations!$C$36:$E$36,XMATCH(Q8,Calculations!$C$35:$E$35,-1))*(IF(Q8<2,1,Q8))*N([@[Chargeable Days]])))

Getting a #N/A

I even tried this =IF(T8="NO",IF(Q8=0,0

 

[ianharper68]

Getting a #N/A

I even tried this =IF(T8="NO",IF(Q8=0,0

Sorry I didn't check the T Column .... If the T8 cell says NO I get a result of $54 which is correct**, however if the T8 cells says YES then I get a #N/A result

**< 1 LDM x $1.5 x 36 Chargeable Days = $54

 

[Alex Blakenburg]

#NA means not found.
Check the value in row 35 (the range in the match) against what you have in Q8

 

[ianharper68]

#NA means not found.
Check the value in row 35 (the range in the match) against what you have in Q8

Hi Alex,

I think we are nearly there. Yes that was my fault I had 0.5 in cell C36 on the calculations page and it should have been a 1

But the formula only works with *[@[Chargeable Days]] but I get the #Value error in all empty cells.

If I add the *N([@[Chargeable Days]]))) I get a formula error?

 

[Alex Blakenburg]

I'm a bit confused did you copy in the formula I posted into the formula box ?

 

[ianharper68]

I'm a bit confused did you copy in the formula I posted into the formula box ?

=IF(Q8=0,0,IF(T8="NO",INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8)),INDEX(Calculations!$C$36:$E$36,XMATCH(Q8,Calculations!$C$35:$E$35,-1))*(IF(Q8<2,1,Q8))*N([@[Chargeable Days]])))

Thats is what I pasted

 

[Alex Blakenburg]

That already has the n() it so what is the error you are getting ?

 

[ianharper68]

So I am not getting the error now? Strange BUT it is not calculating the Chargeable Days? I just a single date charge?
So I changed to this and it seemed to work by adding the *[@[Chargeable Days]] after the first string ... more luck than judgement

=IF(Q8=0,0,IF(T8="NO",INDEX(Calculations!$C$13:$E$13,XMATCH(Q8,Calculations!$C$12:$E$12,-1))*(IF(Q8<2,1,Q8))*[@[Chargeable Days]],INDEX(Calculations!$C$36:$E$36,XMATCH(Q8,Calculations!$C$35:$E$35,-1))*(IF(Q8<2,1,Q8))*N([@[Chargeable Days]])))

 

[ianharper68]

That already has the n() it so what is the error you are getting ?

Thanks for all your help Alex