Using VB in excel 1

[officemanager2]

Hello: I’m trying to develop a spreadsheet application that will allow for a quick classification of a lot of data. In essence what I’m looking for is the ability for the program to look at three columns and decide whether a mineral is present of absent (based on the numbers below) in a given interval, and then based on what chemicals are present or absent that interval is classified as a specific type.
K >=3 Present
K <3 Not Present
Na >=2 Present
Na <2 Not Present
C >=5 Present
C <5 Not Present

This would lead to seven potential outcomes or as I was saying before types:
K Na C Type 1
K Na Type 2
K C Type 3
K Type 4
Na C Type 5
Na Type 6
C Type 7

I would like to set this up as VB code because this is the beginning and if things go well there will be 11 variables, as well I would like to find a way to flag or signal when certain variables far exceed the score. An example would be if the K exceed a score of 20.

Going by VB I'm thinking the declarations would be something like:

Dim K as Integer ( or should this be Boolean)
Dim Na as Integer ( or should this be Boolean)
Dim C as Integer ( or should this be Boolean)
Dim Type1 as String
Dim Type2 as String
Dim Type3 as String
Dim Type4 as String
Dim Type5 as String
Dim Type6 as String
Dim Type7 as String

If K	>=3	ThenPresent
Else If K<3 Not Present

And so on for the rest

Which would be followed by

If K Present, Na Present, Ca present Then Type 1
Else If K present, Na Present, Ca No Present Then Type 2

And so on for the rest

I have over simplified this code but it seemed to me the best starting point. I did not want to write out too much in case I am way off base on how to set this up.

thanks

 

[SkipVought]

Please post a sample of the TABLE of data you will analyze.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[officemanager2]

I believe this is what you are requesting. This something I've typed out there is no code behind it.

Interval K Na Ca Output
1 to 2 1 1 1 type 1
3 to 4 2 2 0 type 2
5 to 6 2 0 2 type 3
7 to 8 3 0 0 type 4
8 to 9 0 5 5 type 5
10 to 11 0 5 0 type 6
11 to 12 0 0 3 type 7

 

[SkipVought]

I am not sure what result you expect.

Here's what I would GUESS from your table, where N is not present and P is present...
[tt]
Interval K Na Ca Output RESULT
1 to 2 1 1 1 type 1 N N N
3 to 4 2 2 0 type 2 N P N
5 to 6 2 0 2 type 3 N N N
7 to 8 3 0 0 type 4 P N N
8 to 9 0 5 5 type 5 N P P
10 to 11 0 5 0 type 6 N P N
11 to 12 0 0 3 type 7 N N P
[/tt]
Just a GUESS!!!

You reaaly have not supplied very much clear, cogent information.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[officemanager2]

It is the output column that I am looking to fill in with the VB. Ideally the code would be able to look at the interval, determine whether the elements are present or not present, based on being above or below a number. From there determine and then put into the output column what is the type (1,2,etc) of interval.


Interval K Na Ca Output
1 to 2 1 1 1
3 to 4 2 2 0
5 to 6 2 0 2
7 to 8 3 0 0
8 to 9 0 5 5
10 to 11 0 5 0
11 to 12 0 0 3

 

[SkipVought]

whether the elements are present or not present

Then WHY did you include the present/not present table???



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[officemanager2]

Well my thinking is/was that first the code has to determine whether within the interval the element(s)are or are not present then based on which of the three elements are or are not present it then will assign a type to the interval.

I thought having the output filled in would be helpful, clearly it was not so I apologize for the confusion.

 

[SkipVought]

Table starts in A1.
[tt]
1. Insert ROW above ROW 1, shifting everything down 1 row.

2. E1: 4
F1: 2
G1: 1
H2: Output

3. E2: =COUNTIF(B2,">0")*E$1
copy this into E2:G8

4, H2: ="TYPE "&8-SUM(E2:G2)

The result

Interval K Na Ca 4 2 1 Output
1 to 2 1 1 1 4 2 1 TYPE 1
3 to 4 2 2 0 4 2 0 TYPE 2
5 to 6 2 0 2 4 0 1 TYPE 3
7 to 8 3 0 0 4 0 0 TYPE 4
8 to 9 0 5 5 0 2 1 TYPE 5
10 to 11 0 5 0 0 2 0 TYPE 6
11 to 12 0 0 3 0 0 1 TYPE 7

[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[officemanager2]

Wow, that is very straight forward and workable. I'll probably have to post something again in a bit as I want to try and expand this sheet. Thank you for your time on this and I'm open to any other input. Thanks again.

 

[SkipVought]

It is not clear what you want to accomplish on this sheet. So you are in some way comparing potassium, sodium and calcium values. WIll it ALWAYS and ONLY be these electrolytes? No magnesium? Others?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[officemanager2]

There will be others, eleven in total. My feeling was I should reduce this down for communication purposes, but clearly there was still an issue so I could have gone with all eleven initially.

The 'types' will become alteration patterns once they are agreed upon. For this purpose it seemed easier to use type1, type2 etc the idea being this could be put through a lookup that would have corresponding alteration types.

There can be as many as 1000 - 6000 intervals on a sheet so the hope is this will allow an opportunity to quickly classify the intervals based upon the elements present. From there the information will be used in different manners, perhaps graphs or simply mean, medium, mode.

Ideally this will advance further and there will be an opportunity to work within the elements so if K is above a certain threshold (say 50 for example) then it might be classified as a different type of alteration if perhaps the K were below 20. The ideal would be to look at each element individual, but corresponding it with other elements at the same time because they are all within the same interval.