Posts by etaf


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: Excel nested (ifand) 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 valuesotherwise 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 15else if(ratio>20&loss<500&balance<100
print 15what 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 appliedsee 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 valueso 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 usingControl+shift+enter
so that {} brackets are around the formulasee 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
looks OK from an assignment of values
this is the issue
=$N2>$L2in 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>$L4also apply to $M$4:$M$34  to cover the complete column