Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

How do I get a 'computed' cell to properly become part of an OR statement in Excel 2010?

Hello,

I have a spreadsheet that I am trying to use to keep track of inbound and outbound shipments, plus have it compute a running inventory total after each transaction.  I am also trying to keep the sheet 'clean' by having it not post anything to the totals cells if there are not inbound or outbound shipments entered.

The inventory portion of the sheet is here:
 inventory calculations
If there are any bags in (D7) and I have entered the bag weight (E7), then D7 and E7 are multiplied together and the total bagged weight entered into F7, else leave the cell blank (formula =IF(AND(D7<>"",E7<>""),D7*E7,"").  This gets me the total inbound bagged weight, F7.  G7 is bulk inbound shipments, and H7 is bulk outbound shipments.

To calculate the total inventory while keeping the sheet 'clean', I want Excel to look first and see if there is any data in the total bagged weight in (F7), inbound bulk shipments (G7), or outbound bulk shipments (H7), take the previous total inventory (I6) and add to it the inbound bagged weight (F7) and inbound bulk weight (G7) and subtract any outbound shipments (H7), and place the new total in I7.  If there is no data in any of F7, G7, H7, leave I7 blank.  My formula looks like this:  =IF(OR(F7<>"",G7<>"",H7<>""),I6+F7+G7-H7,"").

This formula works fine, as long as you have something in F7.  However, if you try to just place a bulk shipment in our out (G7, H7), the formula fails with 'A value used in the formula is of the wrong data type'.

If I delete the formula that calculates F7, the total inventory formula in I7 works fine.  I suspect this means that the OR statement in I7 reads the formula in F7 as some type of data, rather than seeing a blank cell.

Does anyone know how I can correct or work around this?  It seems like it should be simple (I expect it is), but I can't seem to find the solution.
0
meelnah
Asked:
meelnah
  • 6
  • 6
1 Solution
 
jimyXCommented:
You can try and use "NOT(ISBLANK())":
=IF(OR(NOT(ISBLANK(F7)),NOT(ISBLANK(G7)),NOT(ISBLANK(H7))),(I6+F7+G7)-H7,"")

Does it make any difference?
0
 
meelnahAuthor Commented:
nogo... now I receive the 'A value used in the formula is of the wrong data type' error while all fields are blank.

It looked promising though!
0
 
jimyXCommented:
Can you attach a sample sheet please so I can work on.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
meelnahAuthor Commented:
Sure... thanks for taking a shot at it!
ShreveportInventoryWorking.xlsx
0
 
jimyXCommented:
Add "ISERROR":
=IF(ISERROR(OR(NOT(ISBLANK(F7)),NOT(ISBLANK(G7)),NOT(ISBLANK(H7)))),"",(I6+F7+G7)-H7)
0
 
jimyXCommented:
Sorry forgot the if part:
=IF(ISERROR(IF(OR(NOT(ISBLANK(F7)),NOT(ISBLANK(G7)),NOT(ISBLANK(H7))),I6+F7+G7-H7,"")),"",I6+F7+G7-H7)
0
 
meelnahAuthor Commented:
does that work for you?  still getting an error when i try that formula
0
 
meelnahAuthor Commented:
sorry... too soon

tried new formula... no error, but it doesnt calculate anything if you enter only bulk in'=s or puts (g7, h7)
0
 
jimyXCommented:
can you send your sheet with the figures filled in please?
0
 
meelnahAuthor Commented:
wait!  i must have had something else in the field... i recopied your formula and it works perfectly!

can you tell me what the iserror argument is doing?
0
 
meelnahAuthor Commented:
perfect!  thanks!
0
 
jimyXCommented:
ISERROR is used to check if an error exist or result in a cell or formula and it is useful to avoid #ErrorType's.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now