Posts by EgoProwler

    Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result


    Quote from steveorg;621703

    EgoProwler: "For Each cell' defines the length of the loop. If I didn't understand your question properly, I'm not sure it still applies since the logic is a little different.

    Thanks, stevorg. I think you answered the question and are confirming what I thought - that the size of the defined range represents the length of the loop in the "For Each cell" loop.

    Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result


    I'd like to confirm my understanding of what this UDF is doing. I commented each line with what I believe is occurring at each line. The only thing I can't seem to get my head around is how it steps through each cell so you don't keep looping through the range. Is the loop count equal to the size of the range then? So if the range is 4 cells in size, the For Each loop will end at the last cell in the range and process the ConcatenateRange line of the script? Thanks in advance!


    I'm using a UDF from a previous post (http://www.ozgrid.com/forum/showthread.php?t=70327) that works as expected except when the concatenated cell range is blank. I'm admittedly over my head when it comes to VBA and I'm getting nowhere fast determining why the cell containing the UDF has a result of '0' when the range is blank -- I need the result to be blank.



    Changing the Number Format of the cell doesn't seem to make any difference in the final result. Not that it should, I was just taking a stab in the dark this was somehow related to my results.


    My separator as defined in the reference cell is a period, =ConcatenateRange(I6:L6,"."). I see no place in this Function to the best of my interpretation results in a '0', rather than a blank cell.


    If I understand correctly, if the cel.Value = "" (empty) OR cel.Value = 0 then the result should be empty or the separator defined by ".".


    I'm stumped :(

    Re: Concatenate Range With Separator


    This solution works for my particular dataset; however, I'm getting an additional field separator added to the beginning of the range that I don't need. The concatenated result should look like '55.10.25'. yet the actual results are '.55.10.25'.


    Being a complete VBA novice; OK - I know nothing about VBA, I'm not sure how to debug this script and modify so that it does not insert a separator at the beginning of the concatenated string.

    Re: LOOKUP alternative not requiring ascending order sort


    I read over the INDEX / MATCH examples at Contextures and Example 4 appears to be the closest to my data set; with the exception that I need to match the lookup_value between a high and low value. I'm sure there is a way to modify the formula to perform this type of comparison, but I don't even know where to begin with that.


    I attached a sample workbook that shows the current formula (column highlighted in orange) I use to return the data. As long as the cidr and start columns are sorted by ascending order, the results are accurate. I cannot guarantee that the end-user will have these columns sorted to insure accurate results. The other concern I have is that Example 4 uses an array formula. My data set contains over 17000 rows -- will this adversely impact performance?


    I have read multiple VLOOKUP options, but none seem to address a lookup between a high and low value. Perhaps I'm searching for the wrong thing, but I haven't been successful in getting a result that points me in the right direction.


    Thanks for any direction.

    Thanks in advance for any and all advice/direction. I'm struggling with an alternative to a particular problem and searches here are not turning up results to point me in the right direction.


    The workbook contains multiple sheets, but the two pertinent to this question are; hosts and host_lookup. The hosts sheet contains a list of IP addresses representing unique hosts on a network, and the host_lookup sheet contains 3 columns of data; start_ip, end_ip and cidr. The start_ip and end_ip columns are tied to their CIDR equivalent (i.e. 10.10.0.1/24) and represent the usable range of IP addresses for the network.


    I currently use the LOOKUP formula =LOOKUP(H2,A2:B582,C2:C582), where H2 is a host address, A2:B582 being the range of start/end IP addresses and C2:C582 representing the resulting CIDR notation for the range. The formula returns the correct data, but only if the start/end ranges are sorted by ascending order. I'm looking for an alternative to this that does not require sort order of any kind. In short, perform a lookup between two values (i.e. low to high) and return a value from another column on the same row.

    Re: Count where individual cell results product of formula



    daddylonglegs,


    That was it! It didn't even cross my mind that because the numeric result in my formula was wrapped in quotes that Excel would treat it as text. Learn somethin' new everyday.


    Thanks for pointing me in the right direction and have a great day!

    I need to count numeric cells in a column where the result in each cell is the result of a nested IF condition. I didn't see any other thread that addressed this situation.


    Is this even possible without some VBA magic? Unfortunately, I know nothing of VBA, so I don't know that this is even an option. I have tried COUNT and COUNTIF with inaccurate results. I have also made sure that the cells are appropriately formatted as a number.


    Thanks in advance.

    Re: Lookup With 2 Criteria & Duplicates Exist


    Quote from miko68

    Is that what he meant by dynamic named range? I thought it has something to do with the "offset" formula. Well I know that CTRL+SHIFT+ENTER thing, just didn't know that it was called dynamic name range, I feel stupid.:smile:


    CTRL + SHIFT + ENTER makes a formula an array formula. When you look in the formula bar in Excel you would see your formula surrounded in brackets { }.


    I would read http://www.ozgrid.com/Excel/DynamicRanges.htm to get a good understanding of the Dynamic Named Range concept.

    Re: Exceeded Nested IF Suppressing #DIV/0!


    Dave,


    I did think about a lookup, but I wasn't really sure if it was the most efficient solution. I will likely try this as an option in my next revision of the Production worksheet.


    Thanks for another alternative :)

    Re: Suppress #DIV/0! Alternatives


    Quote from ByTheCringe2

    If it's giving the wrong result, it is because you got your columns mixed up:



    Those two statements are contradictory. To correct, swap the Bs and Cs in the formula.


    You are correct, sir, it should have been the other way around :) Your solution DID work, however!


    You introduced a new function to me, MAX, that I'll be doing some reading on now and put into my 'Excel Toolbelt'. Thanks a ton and appreciate everyone's time and efforts!

    Re: Suppress #DIV/0! Alternatives


    Quote from ByTheCringe2

    Try:


    =(SUMPRODUCT((B3:B5)*(D3:D5="Y")*(E3:E5<>"Y")*(F3:F5<>"y")))/MAX(1,SUMPRODUCT((C3:C5)*(D3:D5="Y")*(E3:E5<>"Y")*(F3:F5<>"y")))


    This one partially works. It gives zero result, however when there is a deal(s) that are NOT in-house the Yield Result is not accurate - it's 709.47%. I'll dissect the formula and see why it's this result. Thanks for you help, TheCringe2 -- it gives me some more food for thought :)[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from EgoProwler

    This one partially works. It gives zero result, however when there is a deal(s) that are NOT in-house the Yield Result is not accurate - it's 709.47%. I'll dissect the formula and see why it's this result. Thanks for you help, TheCringe2 -- it gives me some more food for thought :)


    I got it fixed to give the correct result:


    =(SUMPRODUCT((C3:C5)*(D3:D5="Y")*(E3:E5<>"Y")*(F3:F5<>"y")))/MAX(1,SUMPRODUCT((B3:B5)*(D3:D5="Y")*(E3:E5<>"Y")*(F3:F5<>"y")))


    The Volume and Calc ranges were flip flopped. I'll plug this into my 'Production' work sheet and see if this fixes my issue. Thanks again, TheCringe2.

    Re: Suppress #DIV/0! Alternatives



    I'm using Excel 2007. I know I can easily handle the nested IF limitation, however, this sheet has to work in Excel 2003 or older, darn you 7 nested IF limit!. Your second example gives me another course of action where I might be able to suppress the word TRUE with some Conditional Formatting. Incidentally, your second example appears to work in this case.


    All I'm trying to do is suppress any TRUE, FALSE or #DIV/0 errors to the end-user.


    SHG, I'll give your examples a go and report back her.

    Re: Suppress #DIV/0! Alternatives


    Quote from ByTheCringe2

    What should be in cells D9 and E9? What are the formulas doing?


    The formulas in D9 and E9 are accomplishing the same thing -- only in two different attempts by me - merely examples of my attempts by other means.


    Here is what the formula does.


    The first part performs a SUM of the data Column C (Calc) only when Commit = "Y" AND In-House and Funded are not equal to "Y" or blank. It then performs the same SUM on the data in Column B (Volume) with the same "Y" or blank criteria and divides the result by the SUM results in Column C.


    Basically, it's Calc / Volume = Yield. My formula only sum's data that matches the criteria.


    When there is nothing Committed or In-House the result should be ZERO, but I get the #DIV/0 error I want to suppress.

    I'm struggling with an alternative method of suppressing the #DIV/0 in my worksheet. I'm familiar with the ISERROR function and it's use as well as using =IF(A2=0,"",A1/A2), however I'm still getting #DIV/0 errors and I can't use ISERROR because I have exceeded the number of nested IF's.


    I've attached an example. In the example, the only time the #DIV/0 appears is when "Y" appears in the Commit and In-House column for all rows. This is the condition I'm trying to suppress the #DIV/0 error for.


    All the formula's I'm using are Array Formula, as are all my attempts to suppress the error.


    Original Formula
    =SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5)))))))


    (A2=0,"",A1/A2) Method
    =IF(SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5)))))))=0,"",SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5))))))))


    I looked at another possible solution that Dave posted that uses Custom Formatting on a per cell basis, but I can't seem to get that working either.


    Quote from Dave

    To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where 0.00 is desired format for non zeros. Note the use of -0.00 for negatives.


    Any alternatives or suggestions are greatly appreciated.

    Files

    • example.xls

      (19.46 kB, downloaded 137 times, last: )