i've never done more than really basic stuff with excel. enter some data, make a chart, etc.

i'm using a plug-in called PI that pulls sampled data from a server at whatever date/time/interval i want. apparently it uses an array formula to do this. i want to sort that data, but when i do it tells me i can't modify part of an array.

is there any way around this?

7/9/2008 8:32:29 AM

you maybe/probably can't sort the values that are displayed from the array formulas.
but can you Copy then Paste Special > Values all the data then sort it?

7/9/2008 8:49:12 AM

hell yes

awesome

thank you

7/9/2008 12:46:18 PM

PI??!!! They have that at BNP!

7/9/2008 5:22:08 PM

what kind of retard would name a database function "PI"

no wonder India is taking over.

7/9/2008 6:56:16 PM

Quote :

"what kind of retard would name a database function "PI""

I think it stands for Plant Information. From when I've seen it the system is pulling data [sort-of] real time data from various sensors. Could be anything from temperatures, to motor RPMs, water pressure, whatever you're trying to monitor.


LimpyNuts, you work at BNP? what group are you in?

7/9/2008 7:00:55 PM

We use PI at work. I never use the array function, but rather I create a column of timestamps and then use the "Archive Value" function instead. You can dynamically point to the timestamp-to-the-left, just like any other excel function.

7/9/2008 7:20:31 PM

I always hear that people use Excel for complicated programs, and I always shudder.

7/9/2008 8:59:00 PM

You can change the world with some VBA macros and PI...

7/9/2008 9:13:39 PM

^^ i agree that Excel is used way too often in place of a real database and a custom frontend, but seriously - it can do some crazy stuff with built-in functions, then add a dash of VBA and you can go nuts. As with any program/language, though, there's always a point of diminishing return, where you should ditch Excel and go with something else

7/10/2008 12:19:09 AM

i never came back to check this thread

this is one of the things i'm working on with my internship on the fossil side of PGN. it seems pretty powerful and i'm sure i'm not using it to its full capabilities. it's better than nothing though. hell, the senior engineers i'm working with didn't even know you could use PI with excel. i made the mistake of figuring out how to do it and now they keep coming to me with data they want pulled and trended.

i'm using it for several things. cooling lake temps, generator seal oil temps, combustion turbine lube oil temps/pressures. fun stuff.

7/11/2008 11:45:15 PM

^ Yep. I use it to trend reactor core parameters, peaking factors, assembly power shapes, etc.

7/14/2008 12:41:20 PM

we use it too for bioreactor monitoring, trending and archival

7/16/2008 11:26:05 PM

not worthy of a new thread but how come &IF statements interfere with the truncation or rounding of the value in the cell?

I have this formula in a cell:

=IF($B8="Straight",($F8*($N8^$G8)/($I8^$H8))*$M8*$O8,"")&IF($B8="Fitting",P8*O8,"")&IF($B8=U7,V7,"")&IF($B8=U8,V8,"")&IF($B8=U6,V6,"")

The cell is supposed to output a different value based on the value inside of a dropdown box somewhere else on the page. If the box says straight it calculates a value, if it is a fitting it calculates it another way, and after that there is a table that the user is able to input set values into and if the drop down box has that textual value then it uses the fixed number assigned to that value. It does all of that correctly but displays the calculated answers like 0.052429861 instead of 0.05 even though I have the formatting set. (It worked fine before I added the &IF to give the user custom overrides to the formulas.

[Edited on July 31, 2008 at 8:28 AM. Reason : ]

7/31/2008 8:28:02 AM

wrap the output in a ROUND() function.

e.g.
ROUND($F8*($N8^$G8)/($I8^$H8))*$M8*$O8,2)

7/31/2008 8:41:46 AM

haha duurrrr. thanks a lot. Ive gotten so used to always just doing format to designate digits I totally blanked out on the fact that you could do that

7/31/2008 8:55:55 AM

Two sets of data, a few columns each.
Trying to make a third set compiling the results of each.



DataSetA

Name1 Number1
Name2 Number2
Name3 Number3


DataSetB

Name1 Number4
Name2 Number5
Name3 Number6
Name4 Number7


Resulting DataSetC

Name1 Number1 Number4 --> Average(1,4), Rank
Name2 Number2 Number5 --> Average(2,5), Rank
Name3 Number3 Number6 --> Average(3,6), Rank
Name4 [blank] Number7 --> Average(blank,7), Rank


How to set up Data Set C:

Name1; VLOOKUP([Name1],[DataSetA],2,FALSE); VLOOKUP([Name1],[DataSetB],2,FALSE); average; rank
Name2; VLOOKUP([Name2],[DataSetA],2,FALSE); VLOOKUP([Name2],[DataSetB],2,FALSE); average; rank
Name3; VLOOKUP([Name3],[DataSetA],2,FALSE); VLOOKUP([Name3],[DataSetB],2,FALSE); average; rank
Name4; VLOOKUP([Name4],[DataSetA],2,FALSE); VLOOKUP([Name4],[DataSetB],2,FALSE); average; rank


However, the problem comes in on Name4, which does not have a value in DataSetA. The VLOOKUP returns #N/A, which proceeds to fuck up my average and rank (not to mention looks ugly as hell).

I've tried:
=IF(VLOOKUP([Name1],[DataSetA],2,FALSE)<>"",VLOOKUP([Name1],[DataSetA],2,FALSE),"")
Thinking the logic is:
IF VLOOKUP exists, THEN return VLOOKUP, ELSE return ""
But that doesn't work for Name4, where there is a value in DataSetB but not DataSetA. I still get #N/A as the result. It works fine for the entries where Name exists in both datasets.





[Edited on July 31, 2008 at 10:15 AM. Reason : ]

7/31/2008 10:15:03 AM

use ISERROR() to return TRUE if the a VLOOKUP is #N/A, then output "" as a blank cell
The resulting function is ugly and redundant, but it works. I think i read something about Excel 2007 fixing this, where you could specify a default value if a VLOOKUP returned an error....


IF(ISERROR(VLOOKUP([Name1],[DataSetA],2,FALSE)),"",VLOOKUP([Name1],[DataSetA],2,FALSE))

7/31/2008 11:12:54 AM

simple question: how do you assign a numerical value to a letter or symbol, so they can be summed in a column?

8/17/2008 3:55:37 PM

insert --> name --> define

you can make a letter or word equal to a number or function, which seems to be what you're wanting to do

8/17/2008 7:10:39 PM

that doesn't really sound like what he's going for, and I'm not sure it works anyway. I tried it, e.g. assigning the name "one" to 1, "two" to 2, etc. Then entering "=one" in the cell just replaces it with "1" instead

Personally, I would do another VLOOKUP and use a secondary column. Make a lookup table somewhere where you equate letters/symbols in the first column to a number in the 2nd column. Then in your main table where you do the sum, put the letters/symbols in the first column and a VLOOKUP in the next column, then do the sum on that column. You can then hide that column if you want (and move the sum formula to the bottom of the letters/symbols column)

8/17/2008 8:17:51 PM

ok, thanks i got it.

8/17/2008 9:32:29 PM