Basic IF/OR/AND formula syntax, to analyse a number value series and return Yes or No or NoChange as to whether the value is trending up or down..?

MickFish

New Member
Joined
Sep 27, 2017
Messages
7
I wish to analyse a number series and determine if the latest number is higher or lower when compared to previous number values..?

=IF(AK2<>"",IF(AK2=5,"NO",IF(OR(AND(AK2<>"",AK2<AH2),AND(AE2<>"",AK2<AE2),AND(AB2<>"",AK2<AB2),AND(Y2<>"",AK2<Y2),AND(V2<>"",AK2<V2),AND(T2<>"",AK2<T2),AND(Q2<>"",AK2<Q2),AND(N2<>"",AK2<N2),AND(L2<>"",AK2<L2),AND(J2<>"",AK2<J2),AND(G2<>"",AK2<G2),AND(E2<>"",AK2<E2)),"YES" then etc etc for the No and NoChange

Example data:
[TABLE="width: 1392"]
<colgroup><col width="64" style="width: 48pt;" span="29"> <tbody>[TR]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.4 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q2 -15-16[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.5 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.6 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]3.7 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q3 -15-16[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.1 Risk[/TD]
[TD="class: xl34888, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q4 -15-16[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.3 Risk[/TD]
[TD="class: xl34888, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.4 Risk[/TD]
[TD="class: xl34888, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q1 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.5 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q2 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.6 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q3 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.7 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[TD="class: xl34886, width: 64, bgcolor: #C4D79B"]Q4 -16-17[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]4.8 Risk[/TD]
[TD="class: xl34885, width: 64, bgcolor: silver"]Score[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Very High[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Very High[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]Very High[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl34887, bgcolor: #C4D79B"] [/TD]
[TD="bgcolor: transparent"]Moderate[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Simplified data:
[TABLE="width: 528"]
<colgroup><col width="64" style="width: 48pt;" span="11"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
Sometimes there is no value, and the formula needs to ignore these Null values..

Thanks kindly for your time..
MickFish










ddd
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Your formula is invalid, the forum may have corrupted it (places spaces before and after any < > characters).

And what cell is AK2 in your diagram?
 
Last edited:
Upvote 0
[TABLE="width: 1472"]
<colgroup><col width="64" span="23" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl24, width: 64"]1[/TD]
[TD="class: xl24, width: 64"]2[/TD]
[TD="class: xl24, width: 64"]3[/TD]
[TD="class: xl24, width: 64"][/TD]
[TD="class: xl24, width: 64"]4[/TD]
[TD="class: xl24, width: 64"]5[/TD]
[TD="class: xl24, width: 64"]6[/TD]
[TD="class: xl24, width: 64"]7[/TD]
[TD="class: xl24, width: 64"]8[/TD]
[TD="class: xl24, width: 64"]9[/TD]
[TD="class: xl24, width: 64"]10[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD="class: xl24"]score[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl24"]5[/TD]
[TD="class: xl24"]5[/TD]
[TD="class: xl24"]5[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"]4[/TD]
[TD="class: xl24"]3[/TD]
[TD="class: xl24"]3[/TD]
[TD="class: xl24"]4[/TD]
[TD="class: xl24"]4[/TD]
[TD="class: xl24"]3[/TD]
[TD="class: xl24"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]down[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]I am comparing the last score with the three previous scores[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 20"]=IF(L3<(OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,2),$B$1:$L$1,0))+OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,3),$B$1:$L$1,0))+OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,4),$B$1:$L$1,0)))/3,"down","up")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 13"]note that comparing a single value with a previous value, or the average of some prfevious values is not a statistically valid definition of a trend[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks Special-K99
Hopefully this is clearer. I very much appreciate your attention.
Clumsily tries to check if a cell is firstly not Null (<>), then check if value K3 is lower ('Yes'), higher ('No') or equal to ('No Change') the preceding values (A:J). It does appear to return the desired result, but I am uncertain of the accuracy (it is dragged down 000's of rows) or if it truly returns an OVERALL up or down 'trend'..??
The formula:
=IF(K3<>"",IF(K3=5,"NO",IF(OR(AND(J3<>"",J3<K3),AND(I3<>"",J3<I3),AND(H3<>"",K3<H3),AND(G3<>"",K3<G3),AND(F3<>"",K3<F3),AND(E3<>"",K3<E3),AND(D3<>"",K3<D3),AND(C3<>"",K3<C3),AND(B3<>"",K3<B3),AND(A3<>"",K3<A3)),"YES",
IF(OR(AND(J3<>"",J3>K3),AND(I3<>"",J3>I3),AND(H3<>"",K3>H3),AND(G3<>"",K3>G3),AND(F3<>"",K3>F3),AND(E3<>"",K3>E3),AND(D3<>"",K3>D3),AND(C3<>"",K3>C3),AND(B3<>"",K3>B3),AND(A3<>"",K3>A3)),"NO",
IF(OR(AND(J3<>"",J3=K3),AND(I3<>"",J3=I3),AND(H3<>"",K3=H3),AND(G3<>"",K3=G3),AND(F3<>"",K3=F3),AND(E3<>"",K3=E3),AND(D3<>"",K3=D3),AND(C3<>"",K3=C3),AND(B3<>"",K3=B3),AND(A3<>"",K3=A3)),"NoChange","NA")))),"NotInCPM")

Simplified data:
A B C D E F G H I J K
[TABLE="class: cms_table, width: 528"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]score
[/TD]
[TD="width: 64, bgcolor: transparent"]score
[/TD]
[TD="width: 64, bgcolor: transparent"]score
[/TD]
[TD="width: 64, bgcolor: transparent"]score
[/TD]
[TD="width: 64, bgcolor: transparent"]score
[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score[/TD]
[TD="width: 64, bgcolor: transparent"]score
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"]Null
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]3



[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks very much oldbrewer (I am drinking a tasty German beer called Uberbrau)
I looked up the functions you use and nearly follow the logic. Nearly..
Q: Will this formula copy down over 000's of rows? Or do i need to remove the $?
Q: Does $A$1 refer to your top-left cell, value of 1?
Q: Why does your range $B$1:$L$1 not begin from $A?
Q: In the LARGER function, does it matter if the preceeding cells I wish to reference are not sequentially prior to L3? ie you reference 2,3 & 4 whereas my data are 3, 6, 9,...

Thanks again MickFish
 
Upvote 0
copy my formula down as it is
$A$1 is the cell with a 1 in it
it should begin $A$1 well spotted (just checking you were paying attention)
my top row is a helper row in effect numbering the data sequence - do not understand "my data are 3,6,9
 
Upvote 0
Thanks oldbrewer that works, except it throws the #NUM ! error when it encounters a cell with a NULL or blank value.. LARGE(array,k) - if K<=0 #NUM !
Could you please extend your formula back to the Null value cell (D3), showing how you would (possibly) use IF(ISERROR.. to avoid the error?
Thanks again. Where do I send the beer? MickFish
 
Upvote 0
notice in my post 3 there is no number in the top row above the blank - so blanks will not affect it. So just in case there is a n/a in the score cell

=if(iserror(l3),"",IF(L3<(OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,2),$B$1:$L$1,0))+OFFSET($A$1,2,MATCH(LARGE($B$1:$L$1,3),$B$1:$L$1,0))+OFFSET($A$1,2 ,MATCH(LARGE($B$1:$L$1,4),$B$1:$L$1,0)))/3,"down","up"))

note extra close bracket on end

are you in UK if so I will fetch the beer.............
 
Upvote 0
Nah I'm in the colonies, but I do keep and eye on the red devils and both eyes on Rangers. They need another season yet, but I hope to come over next year and watch them get up over Celtic, at Ibrox. I'll be pissing in their Bovril and drinking any live pub beer I can get my hands on..
Thanks again. The bother with the ISERROR is that it returns "" whenever an error is encountered (as it is meant to), when what I really want it to do is disregard and Null values encountered and rather assess what valid values there are across the row array and return the up or down value.

My original clunky IF/OR/AND does this, but not very well.. It didn't copy very well below, but it first checks if the cell value is not blank (<>) then does the three options of Yes No and NoChange. Trouble is it does return a few NA as well.

=IF(K3<>"",IF(K3=5,"NO",IF(OR(AND(J3<>"",J3<k3),and(i3<>"",J3<i3),and(h3<>"",K3<h3),and(g3<>"",K3<g3),and(f3<>"",K3<f3),and(e3<>"",K3<e3),and(d3<>"",K3<d3),and(c3<>"",K3<c3),and(b3<>"",K3<b3),and(a3<>"",K3<a3)),"yes",
IF(OR(AND(J3<>"",J3>K3),AND(I3<>"",J3>I3),AND(H3<>"",K3>H3),AND(G3<>"",K3>G3),AND(F3<>"",K3>F3),AND(E3<>"",K3>E3),AND(D3<>"",K3>D3),AND(C3<>"",K3>C3),AND(B3<>"",K3>B3),AND(A3<>"",K3>A3)),"NO",
IF(OR(AND(J3<>"",J3=K3),AND(I3<>"",J3=I3),AND(H3<>"",K3=H3),AND(G3<>"",K3=G3),AND(F3<>"",K3=F3),AND(E3<>"",K3=E3),AND(D3<>"",K3=D3),AND(C3<>"",K3=C3),AND(B3<>"",K3=B3),AND(A3<>"",K3=A3)),"NoChange","NA")))),"NotInCPM")</a3)),"yes",
</b3),and(a3<></c3),and(b3<></d3),and(c3<></e3),and(d3<></f3),and(e3<></g3),and(f3<></h3),and(g3<></i3),and(h3<></k3),and(i3<>

Anyway oldbrewer, you have been a huge help thanks and I have learnt a lot. You now know I cannot get you the beer. Not this year anyways!
 
Upvote 0
if there is not a number in the top row then whatever is in row 3 is ignored. Cannot see the issue - can you explain it again. (glad to help a bit)
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top