Posts by etaf

Re: Formula to bring back value based on criteria in a column plus 4 columns away

your welcome

Re: Formula to bring back value based on criteria in a column plus 4 columns away

you need another bracket at the end of all the plus
excel does / and * first
so
+INDEX('Raw Data Nov 13'!A:A,MATCH("Green",'Raw Data Nov 13'!G:G,0))
/
(INDEX('Raw Data Nov 13'!A:A,MATCH("Colours",'Raw Data Nov 13'!G:G,0))

will be done first

you need

=(
INDEX('Raw Data Nov 13'!A:A,MATCH("Orange",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Mango",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Other",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Red",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Teal",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Black",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Brown",'Raw Data Nov 13'!G:G,0))
+INDEX('Raw Data Nov 13'!A:A,MATCH("Green",'Raw Data Nov 13'!G:G,0))
)
/

to do the + together

you may have the formatting of the cell to % and returning a number that may explain a 100 error

if still an error

put each formula in to a cell separately and see what result you get for every one
and then use a calc and check the answer

Re: Formula to bring back value based on criteria in a column plus 4 columns away

just use the index/match and add together and divide as normal

= (index(A:A, match("total cases", G:G, 0 )) + index(A:A, match("total cases", G:G, 0 )) + index(A:A, match("total cases", G:G, 0 )+1) )
/
( index(A:A, match("total cases", G:G, 0 )+1) + index(A:A, match("total cases", G:G, 0 )+1) )

obviously the "total cases" needs to change and all on one line - just split to show

Re: Formula to bring back value based on criteria in a column plus 4 columns away

=index(A:A, match("total cases", G:G, 0 )+1)

Re: Formula to bring back value based on criteria in a column plus 4 columns away

index match should do that for you

=index(A:A, match("total cases", G:G, 0 ))

if total cases is in a cell and will change , then put the cell reference in
=index(A:A, match(cell, G:G, 0 ))

Re: Excel nested (if-and) formula

you have missed a false
=IF(AND(BA2<20,AU2<-2000,L2<100),"25",IF(AND(BA2>20,BA2<40,AU2>-2000,AU2<-1000,L2<100),"20",IF(AND(BA2>20,AU2>-2000,AU2<-500,L2<100),"20",IF(AND(BA2>20,BA2<40,AU2>-2000,AU2<-500,L2<100),"15",IF(AND(BA2>20,AU2>-500,L2<100),"15","does not meet criteria")))))

also
"25"
will put the value of 25 into the cell but as text
if you want the numeric vaolue of 25
remove the "" and just put
25
same for all the other values

otherwise a quite check looks ok

you could probably change this to one statement as an OR to combine the two rules
else if(ratio>20&ratio<40&loss<2000&loss>500&balance<100
print 15

else if(ratio>20&loss<500&balance<100
print 15

what happens - is it not working

maybe upload a dummy spreadsheet - with some examples in

Re: Lookup value and return info about the value

have you tried vlookup or index /match

vlookup would be

=vlookup(A1,$B$1:$C$10,2,false)

index/match

=index($c$1:$c$10, match(a1,$B$1:$B$10,0))

Re: Creating fixed graphs

theres probably a better way
but if you use a pivot chart , that should not update until a refresh is applied

see attached

the values where 1,2,3,4 when i created the pivot chart
and then changed the values - and you will see the chart on sheet 1 changed - but the pivot chart on sheet 4 will not change until refresh

Re: Pivot table daily data but monthly targets

can you attach an example spreadsheet here ?

Re: Powerpoint going to the next sheet by clicking

you should be able to use kiosk mode , that should disable the use of mouse and keyboard
slideshow>set up slideshow
OR
you should be able to turn off in slide animation settings

Re: Rename photos to special number

you need to keep the columns you are extracting from the same range
so
=INDEX($A$2:$A$1000,MATCH(MID(C2,1,FIND(".jpg",C2,2)-1) & "*",$B$2:$B$1000,0))

Re: matching similar parts

you could use an IF around your lookup

so if vlookup cannot find the value it returns an error

so iserror(vlookup(....) )
will return true if it cannot find the value

so in an IF

=IF( iserror(vlookup(....)), "", "X")
puts an X if found and leaves blank if not found

Re: Find values and count duplicates in a sheet

your welcome

Re: Find values and count duplicates in a sheet

try
=IFERROR(IFERROR(IFERROR(INDEX($C$2:$C$200, MATCH(0, COUNTIF($G$1:G1, $C$2:$D$200), 0)), INDEX($D$2:$D$200, MATCH(0, COUNTIF($G$1:G1, $D$2:$D$200), 0))), INDEX($E$2:$E$200, MATCH(0, COUNTIF($G$1:G1, $E$2:$E$200), 0))), "")

and
=IF(G2="","",COUNTIF($C$2:$E$200,G2))

Re: Find values and count duplicates in a sheet

what are you now comparing for duplicates - columns or rows ?

if just org1 and 2 - the formula is in for example
G2
=IFERROR(IFERROR(INDEX($C$2:$C$200, MATCH(0, COUNTIF($G$1:G1, $C$2:$C$200), 0)), INDEX($D$2:$D$200, MATCH(0, COUNTIF($G$1:G1, $D$2:$D$200), 0))), "")

Re: Find values and count duplicates in a sheet

sorry dont understand the question, the formula removes all the duplicates from the two lists

perhaps if you loaded a sample spreadsheet

Re: Find values and count duplicates in a sheet

in C2 put
=IFERROR(IFERROR(INDEX($A$2:$A$100, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$100), 0)), INDEX($B$2:$B$100, MATCH(0, COUNTIF($C$1:C1, $B$2:$B$100), 0))), "")
enter using
control+shift+enter as an array formula
in D2 put
=IF(C2="","",COUNTIF($A$2:$B$100,C2))

Re: Need last date vice first date returned in the formula

try using
=MAX(IF(A2=CM!B2:B19, CM!E2:E19))
and enter as an array using

Control+shift+enter
so that {} brackets are around the formula

see attached

if the account cannot be found then it will return a zero (or the date for zero)

=IF(MAX(IF(A2=CM!B2:B19, CM!E2:E19))=0,"No Sale",MAX(IF(A2=CM!B2:B19, CM!E2:E19)))

Re: Compare actual grade to potential grade and highlight cells accordingly

excellent - your welcome

Re: Compare actual grade to potential grade and highlight cells accordingly

looks OK from an assignment of values
this is the issue
=$N2>$L2

in M4 when you goto the cond format is referencing to row 2 and not row 4

high light the M range from row 4
change
=$N2>$L2
to
=$N4>$L4

also apply to $M$4:$M$34 - to cover the complete column