Forum Discussion
Help with conditional columns referencing total amounts
Hi lisago1978
I'm not very clear about your criteria.
As tested in excel, "UCI" is "F" column, "cond" is "G" column.
Could you show me your expected result?
Or clear me more?
Best Regards
Maggie
Thanks for responding and I am sorry I realized the forgot to include the row and column specifications.
Here it is
1 B C D E F G H I 2 Zipcode Count Denominator % yes LCI UCI cond tier 3 90001 28 1389 2.0 1.3 2.9 LOWER 2.0 4 90002 8 167 4.8 2.1 9.4 SAME 3.0 5 90003 36 821 4.4 3.1 6.1 SAME 3.0 6 90004 29 645 4.5 3.0 6.5 SAME 3.0 7 90005 7 146 4.8 1.9 9.9 SAME 3.0 8 90006 23 620 3.7 2.4 5.6 SAME 2.0 9 90007 5 187 2.7 0.9 6.2 SAME 2.0 10 90008 34 559 6.1 4.2 8.5 HIGHER 4.0 11 90009 10 234 4.3 2.0 7.9 SAME 3.0 12 90010 28 789 3.5 2.4 5.1 SAME 2.0 13 90011 46 1261 3.6 2.7 4.9 SAME 2.0 14 Total 858 21846 3.9 3.7 4.2 SAME 4.0 And the formula is =IF(AND(F3<$F$14,G3<$G$14),"LOWER",IF(AND(F3>$F$14,G3>$G$14),"HIGHER","SAME"))
So I am doing a logical formula for the lower and upper CIs. Wherein if the value is lower than both total LCI and UCI it is lower. If the value is higher than both total LCI and UCI than it is higher otherwise it is the same. Let me know what else you need from me.
Thanks
Lisa
In Power Query, Duplicate the table and 'Keep Rows' -> Bottom 1.
This will give you a 1 row table with the Totals line in it. Re-name this table Totals.
In the original table, add a column with this code
if [LCI] < List.First(Totals[LCI]) and [UCI] < List.First(Totals[UCI]) then "LOWER" else if [LCI] > List.First(Totals[LCI]) and [UCI] > List.First(Totals[UCI]) then "HIGHER" else "SAME"
Thank you!
