# Avoiding Double Counting?

Apr 7, 2014

I am trying to avoid double counting two separate rows with the same columns. For example:

StockFund AFund BFund CFund DFund EFund FFund GCountDesired Count
LKOH111100044
LKOH001111042

If I have two stock codes the same in column A (not necessarily one below the other), I only want to count one of the occurrences per fund. So I would like to avoid counting the duplicate records for funds C and D. The desired outcome for the total holdings of LKOH should be 6 and not 8.

## Avoiding Double Vlookup

Jun 9, 2006

I am tring to find a more efficient way of avoiding a double Vlookup function.

=IF(ISNA(VLOOKUP(A6,Sunday!\$A\$72:\$AM\$86,38,FALSE)),"",VLOOKUP(A6,Sunday!\$A\$72:\$AM\$86,38,FALSE))

I have read over this forum but still haven't understood any solutions.

A6 is a name.
The name may not be on the worksheet the function is looking up.

## How To Avoid Double-counting Inventory After It Has Been Accounted For

Apr 5, 2014

I'm trying to generate an inventory outlook/forecast which will show inventory levels for the Monday of every week for 10 weeks. I start with an on-hand stock. Sometime during the course of those 10 weeks there will be an inventory replenishment that will come in. The inventory outlook is generated by simply taking on-hand inventory + replenishment - depletion. I want to make sure that when I account for inventory replenishment on a certain date, I'm counting it only once on the Monday that will follow immediately, and not on subsequent Mondays. I can only think of something like IF(Date>Replenishment Date,Add Replenishment amount,0) type thing, but that would double count on all following Mondays.

I've attached a screen shot with an example. In this example, I'm starting with 50,000 units of inventory. I have the depletion outlook provided by date. On 4/24/2014 I get a replenishment of 25,000 units (which would therefore be accounted for on 4/28/2014, and not beyond that date).

## Avoiding #NUM!

Sep 13, 2005

I've got a couple of formulas for doing in single step:

B A
A B
F D
A F
A #NUM!
D #NUM!
F #NUM!
D #NUM!

but I'm unable to avoid those #NUM!

## Avoiding VALUE When Drag Downwards

Jul 9, 2014

I have a perfect formula

=IF(A2>1,A2/1.16,"")

It does exactly what I need , it does not calculate on empty rows , but it calculates on heading rows , how can I modify to avoid this.

See file attached , I don't want the value on b6 when I drag downwards.

07 09 value avoiding.xls‎

## Avoiding Re-calculation F9

Oct 18, 2009

I have a sheet that requires me to press F9 each time I open it to re-calculate all cells. Why do I need to do this on this 1 sheet? A few months back it was fine and didn't require the extra attention.

## Avoiding #VALUE Error

Jan 25, 2010

Attached is a worksheet to show...

1 - D2- because the supplier has no address it shows up as "0" can you guys help me fix that to show nothing?

2 - Can you guys help me build a formula for E8? Right now I have it as \$C8*\$D8 it works fine until C or D is empty it gives me the #VALUE because I have a formula in D. Because of the #VALUE my formula in E14,15,16 does not work.

## Avoiding \$0.00 Display

Jul 29, 2006

I am multiplying cells and copy down the column to make it easy.

Cell D17, has "=A17*B17" in it. This is fine when there are numbers to compute, but when there aren't, D17 will display "0". How can I have cell D17 remain empty if nothing is put into A17 and B17?

## Avoiding Circular Reference

Jun 25, 2009

Let's say I have a sheet called Category. Column A contains code; column B contains description. Now I have another sheet in which either column F contains a category code or column G contains a category description. I want to look up the code in column F if the description is given or the description in column G if the code is given. I thought I might be able to do something like:

in F2: IF(\$G2<>"",INDEX(Category!A:A,MATCH(\$G2,Category!\$B:\$B,0)),"")
in G2: IF(\$F2<>"",INDEX(Category!B:B,MATCH(\$F2,Category!\$A:\$A,0)),"")

(and then copy the formulae down the rest of the columns of course). In other words, if the cells were both empty then there would be no common dependency; it would just set them both to empty. However, if I provided either value then it would overwrite the formula in that cell so there would be only one formula left and nothing to be circular dependent about.

However, it must be looking at the condition itself and seeing the two columns in common because it is coming up with a circular dependency. I thought I might be able to trick it by having IF(INDEX(\$G:\$G,ROW())<>"",... but it still doesn't work.
This sort of situation must crop up from time to time. How do we get around it?

## Avoiding #value! Errors In My Formula

Nov 17, 2009

I have the formula =today()-g3 which works fine to let me know how many days a loan has been out. However when there is no date in column g i get #value! errors. Can anyone reccomend an alteration to the formula to avoid these errors showing up as they dont look great.

## Avoiding Arrays - Workaround

Sep 10, 2008

How do I avoid using arrays? I am reaching Excel 2003's limit.

Our workbook has many worksheets, each with tables generated from queries that have multiple columns. On the first sheet we have arrays that refer to the other worksheets. Such as:

{=SUM(IF(('Prime Change Orders'!G\$2:G\$10=C17)*('Prime Change Orders'!D\$2:D\$10

## Avoiding Complete Duplicates

Apr 22, 2006

I have a worksheet of Users with columns pertaining to first, middle, and last names. I want to allow duplicates in each column but not a completely duplicated record.

## Avoiding Formulas In Many Rows

May 7, 2006

I have a simple spreadsheet that will be used by others for records management (rehabilitation case management). I have entered the code for most of the 'stuff' that I need to make the workbook easy and simple to use, and get the data back to head office each month.

Because the users will be emailing their records and I haven't yet worked out a way of sending just the "open cases", I need to keep the workbook/worksheets small (and to maintain good programming).

In column L, I have copied this formula from L2:L501:

=IF(ISBLANK(\$K2),"",VLOOKUP(\$K2,LOCSTAT!\$A\$1:\$N\$299,2,FALSE))

Column K is where the case-managed person works and so column L returns the geographical location value. Obviously it's blank until the case is opened by starting a new record and entering each field with data. For example, the user can select " Finance" from a validated drop-down list and the adjacent cell (to the right) will look up the value and return "Parramatta".

How do I translate the cell formula to some VBA?

I suspect that the static VLOOKUP range in the formula (LOCSTAT!\$A\$1:\$N\$299) will need to be dynamic range as well - I can follow the instructions for a single column dynamic range, but I am also getting lost working out how I can create a dynamic range for all data on a dedicated worksheet.

I'll be able to translate a solution to other cells as well

## Avoiding A Save Conflict?

Feb 13, 2007

I have a workbook that multiple users access (read only) to do quick estimates.
This workbook has a "Print & Record" button/macro that does just that... it prints two copies of the estimate, then records/copies the basic data to another workbook (Retail Estimates Data.xls). The macro opens, pastes to, and closes this file in a fairly quick time frame, but it is conceivable that two (or more) users could be trying to do this at the same time.

What I need is some code to check if the Data file is open by another (read only status?), and if so give a message to that effect, and ask to try again, by which time the other user instance should have finished with the file.

## Avoiding #VALUE Errors On Blank Cells

Jun 23, 2008

Is it possible to inset a bank into a cell, and use that cell in a subsequent addition formula?

For example;

A1 contains 1
B1 contains 2
C1 contains =IF(A1=1,"",3)
D1 contains =A1+B1+C1

In the above case the cell D1 contains #VALUE and not 3, whereas if C1 were actually blank D1 would contain 3.

The reason I ask is that a blank is not the same value mathematically as 0. The latter is the number zero, whereas the former is the lack of any number.

## Avoiding Non-numerical Values In A Range

Jan 1, 2009

I have a range, A1:A10 that I want to sum, min, and max. I want to ignore any text or #DIV/0! values in this range. I know how write the formulas except how to ignore the text and cell errors. Can someone steer me in the right direction?

## Avoiding Invalid Worksheet References?

Feb 25, 2014

I am combining the information from multiple files--one per state, for several states--into a single large table so it's more easily reviewed. Each file has multiple worksheets, named for categories. The category/worksheet names are standardized, but not all worksheets are found in every file.

There is some overlap of items from state to state, so my macro copies key fields from each worksheet and pastes them into the new table and adds a column on the left with the name of the category/worksheet each item came from. It then removes any duplicates to create a list of unique items. Across to the right, the table has two columns for each state, one to show if the item exists there, and the other the date it was added.

In the final step I am using lookup formulas to populate the states columns, using the category/worksheet name from the left-hand column to identify which worksheet to pull from. When a worksheet doesn't exist in a source file, however, this creates an invalid reference.

Is there any way to use VBA to identify which worksheets are in a file so I can use the results in an IF/THEN statement to bypass any lines that would create the invalid references?

## Avoiding Exponential Numbers During Import

Dec 16, 2008

I have some short code that imports data and account numbers that is "|" delimited. The problem I am having is that during the import, excel converts some of the account numbers to exponential numbers.

e.g. it converts 542690E24 to 5.4269E+29.

Is there a way to avoid this from occuring during the import? I tried to format the destination cells as '000000000' (all accounts are 9 digits) but it didn't work.

## Avoiding Duplicate Serial Numbers

Nov 20, 2008

I have in my sheet from C13 to C350 many serial numbers which I manually type them in. These serials are 5 digit numbers, and I was just wondering if there's a function or formula that will not allow duplicate numbers in that column.

## Avoiding Text In Array Formula When Using SUM Function?

Feb 4, 2014

I have a problem with an array formula where I want to sum the values in every 7th row. The problem I have is I am receiving #VALUE! in my returing cell of the formula when there is text in the array. Is there a way I can avoid including the text in the array or maybe error checking using ISERROR and ISNUMBER.

The array formula I am entering is: [Code] .........

## Avoiding Display Of Empty Series In Graph

Apr 3, 2007

How can I avoid that an empty series shows up in the legend of a graph?

## Avoiding Copy And Paste In Hidden / Filtered Cells

Aug 5, 2014

I have searched all over and read many solutions for selecting only visible cells and copy pasting them or formatting them by doing Ctrl + G and selecting visible cells only. However the problem is once I select visible cells only, it seems that every time I do Ctrl + C on filtered Range it only selects visible cells. I want a way to toggle this setting in Excel. I'm using Office 2007. For example: if I want to select the entire range, both hidden and visible cells within the selected table array, is there anyway to reverse or toggle the setting that causes Excel to refresh the "visible cells only" setting back to default or all cells?

It would be a much faster way than to remove all filters, select & copy entire range, and then re-apply all filters again.

## Formula To Calculate Result Whilst Avoiding Errors Is Too Long!

Jan 22, 2010

I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.

I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:

## Avoiding User From Inputting Duplicate Entry Into Single Cell In Excel

Dec 3, 2013

I have a worksheet in which i am asking a user to enter manually a number in cell E3. Suppose the user starts with 100, then the next time he is entering in E3 he shoudnt be able to enter 100 nor any number less than 100. I dont have a range for the numbers that the user is going to enter.Therefore countif function does not work. Is there any way that i an store the number taht the user enters first in cell E3 and then use that database to avoid the user from entering the same or a number lesser than that number.????

## Counting Matching Values In Two Separate Ranges Without Counting Duplicates?

Jan 1, 2014

I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.

I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.
However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)

B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.

Range
B1 C1 D1 E1 F1 G1
2 7 19 45 22 13

H1 Total of matching numbers in cell range K1:P11

## Formula Counting Attendance - Counting 1 Day Too Many

Feb 3, 2014

I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls

## Avoiding Average Of An Average: Times

Oct 11, 2006

Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.

so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.

## Double Sum If

Mar 25, 2009

Let me show you how my sheet is set up.

Steeves 10 ny
Steeves 15 ny
Steeves 20 nv
Steeves 5 tn
Steeves 10 ca
Keppler 16 tx
Keppler 20 nj

Now for this example I need to sum column B if A=Steeves and C=NY.

In this example it would return 25.

## Double Inequations

Nov 24, 2008

Is it possible to enter double inequations? Excel returns "false" even it is obviously true. If I type =1<2<3 the result is "false".

I can get the correct result using =and(1<2,2<3), but that makes it longer, and if I have more formulas inside instead of numbers I'll have to repeat the middle one, it will take the place, but only in oreder to compare it again.

Is there anything that can be done with this?

## Is It Possible To Have A Double VLookup?

Aug 19, 2009

On one sheet I have an array of 4 cols. It looks like this:

123 Chris 12345 Jones
123 Chris 23456 Pete
123 Chris 34567 John

On the main sheet there are also 4 cols. In cell A1 I enter a number, in A2 I have a VLookup to get the corresponding name for the number entered in A1, which works fine, I type 123 in A1 and get Chris in A2.

In a perfect world I would like A3 to become a dropdown with 12345, 23456 and 34567 so the user could then select one of those, and by doing so, A4's VLookup would give Jones, Pete or John.

I don't think a dropdown can be populated this way - am I stuck using VBA?