I am trying to build an excel file to take soil lab test results and organize and assign them preliminary labels.
A sample test will include pH, SAR/ESP, and EC readings. Based on those readings I want to assign the results the label Normal, Saline, Saline-Sodic, or Sodic.
Each label has an associated range of values for each criteria, simplest way to visualize what Im looking for is a a graph with two axis (SAR/ESP vs EC) with 4 quadrants. 3 of the quadrants refer to the same pH range.
I have a simple if then setup going right now that basically assigns each result all the possible labels based on each category then assigns it the label that comes up the most. However this is slow and ugly. Is there a way to consolidate this so that when I import a table where each row is a test I can have one column calculating this?
For example ph is evaluated:
=IF($I$2<=8.5,"A B D","C")
With A = Saline, B = Saline-Sodic, C= Sodic, D = Normal. Then SAR is evaluated:
=IF($I$3<=13,"A D","B C")
etc. Then:
=COUNTIF($B$9:$B$12,"A*")
Iterated for each label. The labels are then counted:
=INDEX(Table1[Column1],MATCH(MAX(Table1[Column3]),Table1[Column3],0))
Working properly:
