For alternating bands, you can use the following formula:
=MOD(INT(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2))),2)=1
That's the one.
Thank you very much
Posts by Stephen Bennett
-
-
Hello,
A tiny formula in Conditional Formatting ... :wink:
Hope this will help
Not quite. This is just alternate rows. As you will notice this is in user order. I want the rows for the first user to be one colour, then the next user to be another colour etc
-
Hi hope someone can help.
Please find attached.
I want to alternatively colour the rows of the sheet.
In column A is a list of user names (where the user name appears more than once) and I want to be able to uniquely or alternatively colour the rows based on the usernames.
-
Display More
Hello,
Thanks for your file ... :wink:
Attached is your file with the ' Adjust Colors ' macro ...
Hope this will help
Amazing.
Thank you very much
-
-
Hi,
I have a table with a number of columns that have text that are related.
What I need is a Conditional Format where if I change the colour of cells in Column A the cells in Column B that are in the same row will automatically change to the same colour.
-
Assuming you don't want to just plot each group as a separate series, you could use some code to loop through and colour each point, as long as you have some means for the code to determine which points should be which colour.
Correct.
So what code do I use to make it so I could make all the Lemon plots yellow, plums purple etc?
Forgive me, my code writing is very basic.
-
Hi,
Hope someone can help.
Please see the below example of a table I want to use to make a X Y graph: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 344"]
[tr]
[td][/td]
[td]X
[/td]
[td]Y
[/td]
[td][/td]
[/tr]
[tr]
[td]Fruit
[/td]
[td]WIDTH (cm)
[/td]
[td]LENGTH (cm)
[/td]
[td]VOLUME (cm2)
[/td]
[/tr]
[tr]
[td]Apple 1
[/td]
[td]5.6
[/td]
[td]5.4
[/td]
[td]30.24
[/td]
[/tr]
[tr]
[td]Apple 2
[/td]
[td]5.5
[/td]
[td]5.5
[/td]
[td]30.25
[/td]
[/tr]
[tr]
[td]Apple 3
[/td]
[td]5.9
[/td]
[td]5.5
[/td]
[td]32.45
[/td]
[/tr]
[tr]
[td]Apple 4
[/td]
[td]6.2
[/td]
[td]5.6
[/td]
[td]34.72
[/td]
[/tr]
[tr]
[td]Strawberry 1
[/td]
[td]2.1
[/td]
[td]2.5
[/td]
[td]5.25
[/td]
[/tr]
[tr]
[td]Strawberry 2
[/td]
[td]2.3
[/td]
[td]2.7
[/td]
[td]6.21
[/td]
[/tr]
[tr]
[td]Strawberry 3
[/td]
[td]2.2
[/td]
[td]2.5
[/td]
[td]5.5
[/td]
[/tr]
[tr]
[td]Strawberry 4
[/td]
[td]2.2
[/td]
[td]2.4
[/td]
[td]5.28
[/td]
[/tr]
[tr]
[td]Lemon 1
[/td]
[td]4.1
[/td]
[td]5.3
[/td]
[td]21.73
[/td]
[/tr]
[tr]
[td]Lemon 2
[/td]
[td]4.3
[/td]
[td]5.4
[/td]
[td]23.22
[/td]
[/tr]
[tr]
[td]Lemon 3
[/td]
[td]4.4
[/td]
[td]5.5
[/td]
[td]24.2
[/td]
[/tr]
[tr]
[td]Lemon 4
[/td]
[td]4.2
[/td]
[td]5.6
[/td]
[td]23.52
[/td]
[/tr]
[tr]
[td]Plum 1
[/td]
[td]3.3
[/td]
[td]4.8
[/td]
[td]15.84
[/td]
[/tr]
[tr]
[td]Plum 2
[/td]
[td]3.6
[/td]
[td]4.9
[/td]
[td]17.64
[/td]
[/tr]
[tr]
[td]Plum 3
[/td]
[td]3.4
[/td]
[td]4.8
[/td]
[td]16.32
[/td]
[/tr]
[tr]
[td]Plum 4
[/td]
[td]3.3
[/td]
[td]4.7
[/td]
[td]15.51
[/td]
[/tr]
[/TABLE]
So it is simple enough to plot each fruit on a X Y graph, but what I want to know is there a way that I can colour the plots/data series on the graph according to the type of fruit it is (ie lemon is yellow, Plum is purple etc) but not doing this manually for each plot on the graph.I ask as I am try to do something where there is over 1,000 plots to go on the graph so cannot do it manually for each one.
Thanks
-
Try:
=IFERROR(SUM(B3,D3,F3,H3,J3,L3,N3,P3,R3,T3,V3,X3)/SUM(A3,C3,E3,G3,I3,K3,M3,O3,Q3,S3,U3,W3),0)
That's the one. I feel like such a numpty as I was trying all different ways with the commas and brackets and was so close but just needed a little help.
Thank you very much.:congrats:
-
Hi,
I am a bit stuck here.
Below is an example of what I am working on. So for each month I have the number of Work Orders and the total labour hours.
At the end I want to show the average Labour Hours per Work Order.
That's simple: =SUM(B3,D3,F3,H3,J3,L3,N3,P3,R3,T3,V3,X3)/SUM(A3,C3,E3,G3,I3,K3,M3,O3,Q3,S3,U3,W3)
But if there are only zeros then naturally it will give back an error (#DIV/0!)
Can someone tell me how I can incorporate an IFERROR or IFISERROR into the above formula so it will show all errors as a 0?
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 1681"]
[tr]
[td][/td]
[td]A
[/td]
[td]B
[/td]
[td]C
[/td]
[td]D
[/td]
[td]E
[/td]
[td]F
[/td]
[td]G
[/td]
[td]H
[/td]
[td]I
[/td]
[td]J
[/td]
[td]K
[/td]
[td]L
[/td]
[td]M
[/td]
[td]N
[/td]
[td]O
[/td]
[td]P
[/td]
[td]Q
[/td]
[td]R
[/td]
[td]S
[/td]
[td]T
[/td]
[td]U
[/td]
[td]V
[/td]
[td]W
[/td]
[td]X
[/td]
[td]Y
[/td]
[/tr]
[tr]
[td]1
[/td]
[td][/td]
[TD="colspan: 2"]Apr-18[/TD]
[TD="colspan: 2"]May-18[/TD]
[TD="colspan: 2"]Jun-18[/TD]
[TD="colspan: 2"]Jul-18[/TD]
[TD="colspan: 2"]Aug-18[/TD]
[TD="colspan: 2"]Sep-18[/TD]
[TD="colspan: 2"]Oct-18[/TD]
[TD="colspan: 2"]Nov-18[/TD]
[TD="colspan: 2"]Dec-18[/TD]
[TD="colspan: 2"]Jan-19[/TD]
[TD="colspan: 2"]Feb-19[/TD]
[TD="colspan: 2"]Mar-19[/TD]
[/tr]
[tr]
[td]2
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]No of WO
[/td]
[td]Lab Hrs
[/td]
[td]Lab Hrs/WO
[/td]
[/tr]
[tr]
[td]3
[/td]
[td]0
[/td]
[td]0
[/td]
[td]0
[/td]
[td]0
[/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]#DIV/0
[/td]
[/tr]
[/TABLE] -
try this:
Code=IF(AND(A2=0,B2=0),0,IF(ISERROR(A2/B2),1,A2/(A2+B2)))note: cell c2 formatted as %
Yes!!!!
That did it.
Thank you very much.
-
Hi,
Hope someone can help, this has got me going around in circles. Please see table below:
[tr]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 321"]
[td]Apples
[/td]
[td]Pears
[/td]
[td]Proportion of Apples to Pears (%)
[/td]
[/tr]
[tr]
[td]10
[/td]
[td]0
[/td]
[td]0
[/td]
[/tr]
[/TABLE]
Under Proportion of Apples to Pears (Cell C2) I have put this formula:=IF(ISERROR(A2/B2),0,A2/(A2+B2))
It should come back with the answer 100% but it comes back as 0.
I wish to keep the IF ERROR in the formula because if there are 0 Apples and 0 Pears I want it to still return a 0 rather than a #DIV/0 error.
Can someone help?
-
That's the one!!!!!
Exactly what I needed.
Nice one NBVC. Happy days :saychees:
Please note that if you try this make sure that:
- The rows are in date order (oldest first))
- Then make sure you sort in asset order second (ie each assets dates are oldest to newest)
- Make sure the column format is either General or number. Otherwise you will get a date as an answer not a number
- Once you have entered the formula and copied it all the way down the column, copy the column then paste using the Values (v) option to ensure the numbers stay
-
Hi,
Hope someone can help.
I have a table of three columns (please see below).
The first is a list of Work Order Numbers.
The second is a list of Asset Numbers that the Work Orders are assigned to.
The third is the Report Date, which is a list of dates the Work Orders were raised.
Please can someone tell me a way that I can work out the average time between Report dates for each asset. ie Asset A has an average of XX days between Work Orders, Asset B has an average of XX days between Work Orders etc.
Thanks
[tr]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 241"]
[td]Work Order
[/td]
[td]Asset
[/td]
[td]Report Date
[/td]
[/tr]
[tr]
[td]100001
[/td]
[td]A
[/td]
[td]01-Apr-18
[/td]
[/tr]
[tr]
[td]100002
[/td]
[td]A
[/td]
[td]04-Apr-18
[/td]
[/tr]
[tr]
[td]100003
[/td]
[td]A
[/td]
[td]10-Apr-18
[/td]
[/tr]
[tr]
[td]100004
[/td]
[td]A
[/td]
[td]20-Apr-18
[/td]
[/tr]
[tr]
[td]100005
[/td]
[td]B
[/td]
[td]02-Apr-18
[/td]
[/tr]
[tr]
[td]100006
[/td]
[td]B
[/td]
[td]10-Apr-18
[/td]
[/tr]
[tr]
[td]100007
[/td]
[td]B
[/td]
[td]16-Apr-18
[/td]
[/tr]
[tr]
[td]100008
[/td]
[td]B
[/td]
[td]20-Apr-18
[/td]
[/tr]
[tr]
[td]100009
[/td]
[td]B
[/td]
[td]25-Apr-18
[/td]
[/tr]
[tr]
[td]100010
[/td]
[td]B
[/td]
[td]30-Apr-18
[/td]
[/tr]
[tr]
[td]100011
[/td]
[td]C
[/td]
[td]01-Apr-18
[/td]
[/tr]
[tr]
[td]100012
[/td]
[td]C
[/td]
[td]05-Apr-18
[/td]
[/tr]
[tr]
[td]100013
[/td]
[td]C
[/td]
[td]15-Apr-18
[/td]
[/tr]
[tr]
[td]100014
[/td]
[td]C
[/td]
[td]22-Apr-18
[/td]
[/tr]
[tr]
[td]100015
[/td]
[td]C
[/td]
[td]28-Apr-18
[/td]
[/tr]
[tr]
[td]100016
[/td]
[td]C
[/td]
[td]29-Apr-18
[/td]
[/tr]
[tr]
[td]100017
[/td]
[td]D
[/td]
[td]05-Apr-18
[/td]
[/tr]
[tr]
[td]100018
[/td]
[td]D
[/td]
[td]08-Apr-18
[/td]
[/tr]
[tr]
[td]100019
[/td]
[td]D
[/td]
[td]19-Apr-18
[/td]
[/tr]
[tr]
[td]100020
[/td]
[td]D
[/td]
[td]23-Apr-18
[/td]
[/tr]
[tr]
[td]100021
[/td]
[td]D
[/td]
[td]29-Apr-18
[/td]
[/tr]
[/TABLE] -
Hi
Hope someone can help.
I have a conditional format where A3 will turn green if below the value in A2 and Red if greater than the value in A2.
What do I need to do so I can click and drag or copy the conditional formatting quickly so that B3 will be related to B2, C3 to C2 etc
In the manage rules I have used $A$2 and A$2 and I still can't copy it across. I have also tried Format painter with no success.
-
-
That's great.
Thank you all so much for your help.
-
Sorry,
What I meant was, if I am putting the formula into A3 how do I get A3 to remain blank if there is nothing in A2
-
That's great.
Sorry to be cheeky, but if A2 is blank, how do I incorporate an IF(ISBLANK(A2),"", into it?
-
[SIZE=12px]Hi,
Can someone provide me with a formula that will give me the following scores from these percentage ranges?
Percent Score
<10% 1
10-20% 2
20-30% 3
30-40% 4
40-50% 5
50-60% 6
60-70% 7
70-80% 8
80-90% 9
>90% 10[/SIZE]I have a column with percentages in. I need the scores to go in the cell to the right