i know pivot-table, need summary data in display. means i'm doing other datagathering , pivot won't do.
my table looks this:
date | value | weekday 1.1. | 5903 | thursday 2.1. | 2981 | friday 5.1. | 3046 | monday 6.1. | 5021 | tuesday 7.1. | 6785 | wednesday 8.1. | 5074 | thursday 9.1. | 3631 | friday 12.1.| 7083 | monday 13.1.| 3531 | tuesday 14.1.| 5589 | wednesday 15.1.| 2068 | thursday 16.1.| 6485 | friday 19.1.| 7013 | monday 20.1.| 3802 | tuesday 21.1.| 5291 | wednesday 22.1.| 6142 | thursday i need find max , min values weekday. note table ordered dates. i've heard not-so-great stories vlookup.
so answers be:
max 12.1.| 7083 | monday 6.1. | 5021 | tuesday 7.1. | 6785 | wednesday 22.1.| 6142 | thursday 16.1.| 6485 | friday min 5.1. | 3046 | monday 13.1.| 3531 | tuesday 21.1.| 5291 | wednesday 15.1.| 2068 | thursday 2.1. | 2981 | friday what right formula?
you can use either array or standard pseudo-minif/maxif formulas retrieve value need 2 column lookup retrieve value date column.
the formulas e4:f4,e11:f11 are:
'e4 =index($a$2:$a$9999, min(index(row($1:$9998)+(($b$2:$b$9999<>f4)+($c$2:$c$9999<>g4))*1e+99, , ))) 'f4 =max(index($b$2:$b$9999*($c$2:$c$9999=g4),,)) 'aggregate alternative f4 =aggregate(14, 6, 1/($c$2:$c$9999=g4)*($b$2:$b$9999),1) 'e11 =index($a$2:$a$9999, min(index(row($1:$9998)+(($b$2:$b$9999<>f11)+($c$2:$c$9999<>g11))*1e+99, , ))) 'f11 =min(index($b$2:$b$9999+($c$2:$c$9999<>g11)*1e+99,,)) `f11 zeroes discarded =min(index($b$2:$b$9999+(($c$2:$c$9999<>g11)+($b$2:$b$9999=0))*1e+99,,)) 'aggregate alternative f11 =aggregate(15, 6, 1/($c$2:$c$9999=g11)*($b$2:$b$9999),1) fill each e:f area down necessary. these formula depend upon weekday values in columns c & g being text strings; different approach required if these actual dates formatted mmmm
Comments
Post a Comment