• Welcome to OGBoards 10.0, keep in mind that we will be making LOTS of changes to smooth out the experience here and make it as close as possible functionally to the old software, but feel free to drop suggestions or requests in the Tech Support subforum!

The Excel Help Thread

Not easily. You may need to put a "designator" type reference in the adjacent column on Spreadsheet1 and then use a SUMIF funtion on the second spreadsheet, or use a Pivot Table for your calculations instead.
 
Okay, I can't figure out a good way to do this but if you guys can I'd like to know.

I have two columns, representing an ip range
for example

column a column b
x.x.x.0 x.x.x.31
x.y.z.3 x.y.z.97

is there any easy way to generate all of the individual ip addresses within column a and b here? I want a cell that has x.x.x.1 another with x.x.x.2 etc.

EDIT: it turns out i only needed the first three octets, but I would still like to know if anyone can figure this out.
 
Last edited:
Juice said:
Okay, I can't figure out a good way to do this but if you guys can I'd like to know.

I have two columns, representing an ip range
for example

column a column b
x.x.x.0 x.x.x.31
x.y.z.3 x.y.z.97

is there any easy way to generate all of the individual ip addresses within column a and b here? I want a cell that has x.x.x.1 another with x.x.x.2 etc.

EDIT: it turns out i only needed the first three octets, but I would still like to know if anyone can figure this out.
Click to expand...

I read this three times and still do not understand the goal.
 
Juice said:
Okay, I can't figure out a good way to do this but if you guys can I'd like to know.

I have two columns, representing an ip range
for example

column a column b
x.x.x.0 x.x.x.31
x.y.z.3 x.y.z.97

is there any easy way to generate all of the individual ip addresses within column a and b here? I want a cell that has x.x.x.1 another with x.x.x.2 etc.

EDIT: it turns out i only needed the first three octets, but I would still like to know if anyone can figure this out.
Click to expand...

n00b, let the expert work on it for a min. ill get back to you

also, you want the cells x.x.x.1 and x.x.x.2 in the same column? row?
 
why cant you just copy data onto different sheet and select range and delete duplicates.
 
Juice,

If I understood your initial question correctly, I think I figured it out.

On a blank workbook, if the array

Juice said:
column a column b
x.x.x.0 x.x.x.31
x.y.z.3 x.y.z.97
Click to expand...

is in cells A1:B3 (I typed "column a" and "column b" as my column headers in columns A and B), Copy>Paste Transpose your cells A2:A3 (which should be "x.x.x.0" and "x.y.z.3") into cells D2:E2.

Then, paste the following monstrosity of a formula in cell D3, and drag down:

=IF(D2=""&"","",IF((MID(D2,FIND(".",D2,FIND(".",D2,FIND(".",D2,1)+1)+1)+1,LEN(D2))+1)>VALUE(MID($B$2,FIND(".",$B$2,FIND(".",$B$2,FIND(".",$B$2,1)+1)+1)+1,LEN($B$2))),"",LEFT($A$2,FIND(".",$A$2,FIND(".",$A$2,FIND(".",$A$2,1)+1)+1))&MID(D2,FIND(".",D2,FIND(".",D2,FIND(".",D2,1)+1)+1)+1,LEN(D2))+1))

Paste this formula in cell E3, and drag down (this is the same formula, just referencing your x.y.z set):

=IF(E2=""&"","",IF((MID(E2,FIND(".",E2,FIND(".",E2,FIND(".",E2,1)+1)+1)+1,LEN(E2))+1)>VALUE(MID($B$3,FIND(".",$B$3,FIND(".",$B$3,FIND(".",$B$3,1)+1)+1)+1,LEN($B$3))),"",LEFT($A$3,FIND(".",$A$3,FIND(".",$A$3,FIND(".",$A$3,1)+1)+1))&MID(E2,FIND(".",E2,FIND(".",E2,FIND(".",E2,1)+1)+1)+1,LEN(E2))+1))
 
i think small big tall has got it. The issue is that this gets fucking insane because there are 2,000 rows of this.

for those of you who don't get the issue

i have a column of start ips and a column of end ips and am trying to get every ip within that range. if a1=x.x.x.0 a2=x.x.x.9 b1=x.y.z.5 b2=x.y.z.95 and so on. I wanted x.x.x.1 and x.x.x.2 all the way through x.x.x.9. I wanted x.y.z.6 all the way through x.y.z.95.

I was saved by not having to deal with that last octet (there are no longer ranges if you remove the final octet, as a1=a2 b1=b2 etc.)
 
Last edited:
Ouch. Yeah, with my formulas you'd have to have a column of IPs for each row of beginning/end IPs you've got, which would put you in the columns BAA and beyond...
 
I'm back! With a (hopefully) simple problem for the excel wiz's on the thread.

Nesting IF statements again. What I am looking to do is have another count with 1's and 0's if the cells have certain names in them. The names are in column Z. Column C contains a count from 1-alot with some of the cells being skipped and remaining blank. If column C is blank I don't want the name in column Z included in the count.

Here is the formula I have right now the bolded part is where I'm running into the problem:

=IF(Sheet1!Z3="Craig",1,IF(Sheet1!C3<1,0,IF(Sheet1!Z3="Rob",0,IF(Sheet1!Z3="Christian",0,IF(Sheet1!Z3="Kelly",0)))))

I appreciate the help and I will send positive rep your way!
 
I think I would solve this via a lookup table on another tab. Where column A has the name, Column B has the boolean.

On the main tab, I'd say =vlookup(Sheet1!Z3, LookupTable, 1)

At the least, I think this keeps it cleaner what names get 0 and 1, versus embedding in multiple "if" statements which is just... yuck.

You may need to tighten up the vlookup to say something like
=if(vlookup(Sheet1!Z3, LookupTable, 0) = Sheet1!Z3,...) to make sure the name matches exactly. I'm not sure if newer versions of Excel have new functions to do exact lookups, rather than returning the closest one.
 
Excel help

I've been trying to figure out a formula or find one by google searching but no luck so far. I am trying to set up a spreadsheet to track expenditures year to date versus year to date budget. A simple sum formula would work for the expenditures but I need a fomula to calculate year to date budget amounts and leave out budget amounts for future months.

I know the budgets for each month for FY 2012 (July 2011 to June 2012) and need to be able to do a comparison of actuals versus what the budget is to date. Thanks Pit.
 
Can we get a feature where folks can upload their docs and people can access them to make this easier? That would be BOSS.
 
You must log in or register to reply here.
Back
Top