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
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