Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index Questions
I have an index formula I am using that I want to know if there is something
I could add to have it answer $0. Here is what my spreadsheet looks like, and the formula I am using. =INDEX(cranetest1!$R$3:$R$154,MATCH(Sheet1!B4&$AB$ 2,INDEX(cranetest1!$B$3:$B$154&cranetest1!$Q$3:$Q$ 154,0),0)) --A-------B-------------C---------D Load# Destination-----Desc.----Charges 12345 Columbus, OH----Fuel------$100 12345 Columbus, OH----Stop------$50 12345 Columbus, OH----Linehaul--$150 --H-----------I-----------J--------K-----L Load#---Destination----Linehaul--Stop---Fuel 12345---Columbus, OH-----150------50-----100 If load 12346 does not have a fuel charge I want the formula to return $0 instead of N/A. Is there a way to do that? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index Questions
Hello,
Please describe what you have in all the referenced areas (some sample data). CUrrently I can only guess that you might need to repeat all states in cranetest1!B:B with empty corresponding cells in cranetest1!Q:Q (but this is sort of a wild guess I admit). Regards, Bernd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index Questions
In general:
=IF(ISNA(Your_Formula),0,Your_Formula) -- Gary''s Student - gsnu200909 "TB@work" wrote: I have an index formula I am using that I want to know if there is something I could add to have it answer $0. Here is what my spreadsheet looks like, and the formula I am using. =INDEX(cranetest1!$R$3:$R$154,MATCH(Sheet1!B4&$AB$ 2,INDEX(cranetest1!$B$3:$B$154&cranetest1!$Q$3:$Q$ 154,0),0)) --A-------B-------------C---------D Load# Destination-----Desc.----Charges 12345 Columbus, OH----Fuel------$100 12345 Columbus, OH----Stop------$50 12345 Columbus, OH----Linehaul--$150 --H-----------I-----------J--------K-----L Load#---Destination----Linehaul--Stop---Fuel 12345---Columbus, OH-----150------50-----100 If load 12346 does not have a fuel charge I want the formula to return $0 instead of N/A. Is there a way to do that? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index Questions
Sorry I meant to update the formula to match my example listed. The formula
should look like this. =INDEX(cranetest1!$D$2:$D$5,MATCH(Sheet1!H2&$L$1,I NDEX(cranetest1!$B$3:$B$154&cranetest1!$C$2:$C$5,0 ),0)) I'm wanting to know how to get the formula to return $0 if for instance say load 12345 does not have a fuel charge. Right now it returns N/A. "TB@work" wrote: I have an index formula I am using that I want to know if there is something I could add to have it answer $0. Here is what my spreadsheet looks like, and the formula I am using. =INDEX(cranetest1!$R$3:$R$154,MATCH(Sheet1!B4&$AB$ 2,INDEX(cranetest1!$B$3:$B$154&cranetest1!$Q$3:$Q$ 154,0),0)) --A-------B-------------C---------D Load# Destination-----Desc.----Charges 12345 Columbus, OH----Fuel------$100 12345 Columbus, OH----Stop------$50 12345 Columbus, OH----Linehaul--$150 --H-----------I-----------J--------K-----L Load#---Destination----Linehaul--Stop---Fuel 12345---Columbus, OH-----150------50-----100 If load 12346 does not have a fuel charge I want the formula to return $0 instead of N/A. Is there a way to do that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
Shape color index does not match cell color index | Excel Programming | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
PivotTable.columnfields(index) vs Pivotcell.columnitem(index) | Excel Programming | |||
index random questions | Excel Worksheet Functions |