Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-24
12
Medium Priority
?
324 Views
Last Modified: 2012-05-11
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
Comment
Question by:meelnah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 34977181
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
 
LVL 1

Author Comment

by:meelnah
ID: 34977197
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
 
LVL 24

Expert Comment

by:jimyX
ID: 34977217
Can you attach a sample sheet please so I can work on.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:meelnah
ID: 34977221
Sure... thanks for taking a shot at it!
ShreveportInventoryWorking.xlsx
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34977291
Add "ISERROR":
=IF(ISERROR(OR(NOT(ISBLANK(F7)),NOT(ISBLANK(G7)),NOT(ISBLANK(H7)))),"",(I6+F7+G7)-H7)
0
 
LVL 24

Accepted Solution

by:
jimyX earned 2000 total points
ID: 34977301
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
 
LVL 1

Author Comment

by:meelnah
ID: 34977307
does that work for you?  still getting an error when i try that formula
0
 
LVL 1

Author Comment

by:meelnah
ID: 34977318
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
 
LVL 24

Expert Comment

by:jimyX
ID: 34977328
can you send your sheet with the figures filled in please?
0
 
LVL 1

Author Comment

by:meelnah
ID: 34977341
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
 
LVL 1

Author Closing Comment

by:meelnah
ID: 34977345
perfect!  thanks!
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34977381
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

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question