Excel Multiple Conditional Statements -
hour daypart daypart cluster ------------- ------------------------------- 1 overnight 6 10 morning 6 morning 10 15 midday 15 afternoon 15 19 afternoon 20 evening 19 24 evening 8 morning 24 6 overnight
i need assign 'daypart' grouping (in separate column) reflect daypart cross-reference table based on hour.
for instance, if hour > 10, , hour < 15
, cluster should "midday".
i running problem when because need account other dayparts.
so far conditions in excel result in false:
=if(and(h2>$o$2,h2>$p$2,$q$2),if(and(h2>$o$3,h2<$p$3,$q$3),if(and(h2>$o$4,h2<$p$4,$q$4),and(h2>$o$5,h2<$p$5,$q$5))))
you need change overnight start @ 0 , go 6 move first row of criteria.
i'll start nested set conditional statements. if start single condition no other can meet progress through possibilities need 1 condition each daypart. in sample image below, i2 is,
=if(h2<6, "overnight", if(h2<10, "morning", if(h2<15, "midday", if(h2<19, "afternoon", "evening"))))
as can see, progressive condition subsequent condition can reached if first false, there no need to hour @ all.
if want use cross-reference table, needs reoriented slightly.
with overnight changed start @ 0 hour , moved top starting hour in ascending order, vlookup function can used lookup approximate match on ascending starting hours. in sample image above formula in j2 is,
=vlookup(h2, $o$2:$q$6, 3, true)
fill down necessary. common nested less than or greater than conditions, there ambiguity between less than and/or less or equal to. if results not exact, should easy fix.
Comments
Post a Comment