I have this formula: =VLOOKUP(I7,A1:F33,6,FALSE)*I6
And I would like it to show $00.00 instead of #N/A
I googled it an can't seem to find anything that applies.
I have this formula: =VLOOKUP(I7,A1:F33,6,FALSE)*I6
And I would like it to show $00.00 instead of #N/A
I googled it an can't seem to find anything that applies.
=iferror(VLOOKUP(I7,A1:F33,6,FALSE)*I6,0) and format as currency.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
maybe
=IFERROR(VLOOKUP(I7,A1:F33,6,FALSE)*I6,0)
formated as currency
This one too: =I8*IF(OR(I5>12,I9=0),0,IF(I9=1,0.1,IF(I9=2,0.25,IF(I9=3,0.55,IF(I9=4,0.7,1)*IF(I9=5,1,1)))))
You're awesome, that worked.
That last part of your formula *IF(I9=5,1,1) is irrelevant.
It says that if I9=5 then 1 and if not then 1..so no matter what is in I9 it multiplies the existing number *1...therefore does nothing.
This shorter formula might work for you...but it will only handle 0-5. If there is a 6 it will fail.
=IF(I5>12,0,I8*CHOOSE(I9+1,0,0.1,0.25,0.55,0.7,1))
Otherwise, your existing formula could be:
=I8*IF(OR(I5>12,I9=0),0,IF(I9=1,0.1,IF(I9=2,0.25,IF(I9=3,0.55,IF(I9=4,0.7,1)))))
or
=IFERROR(IF(I5>12,0,I8*CHOOSE(I9+1,0,0.1,0.25,0.55,0.7,1)),1)
I had the same formula you are recommending and when I try entering a value of 5 in I9, it returned #N/A so I added that one in. It seems to be working just fine with what I have in there.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks