Re: assigning points to percentages
your welcome
Re: assigning points to percentages
your welcome
Re: Compare actual grade to potential grade and highlight cells accordingly
added an example sheet to the post above with the formula and better values for A,B etc
and also used the conditional formatting to highlight the two grades
Re: Compare actual grade to potential grade and highlight cells accordingly
you could probably use a lookup to change to a value in some helper columns
=LOOKUP(A11,{"A","B","C","S"},{2,3,4,1})
and then compare the helper columns and conditional format the grades
(note the letters need to be ascending )
how would you compare the A/B S/A
not sure how that would work in the array
EDIT
if you putin alpha order based on first character it will work with the array
so
=LOOKUP(A11,{"A","A/B","B","C","C/D","D","S"},{1,10,2,3,11,4,100})
assigns the values
and then simple enough to compare
you need the helper columns because conditional format wont do the array lookup
see the attached example - with conditional formating
green for higher or equal to
red for lower
Re: assigning points to percentages
you could use a lookup table to assign the extra points
set up a table of % and points
and then use
=INDEX(Point!$B$3:$B$20,MATCH(B3,Point!$A$3:$A$20,1))
to pull the points across - the 1 at the end means its looking for the nearest lowest value if it cannot find an exact match
the ranking will show duplicates if the points are the same - you maybe able to use something else to differentiate between those rankings -
see attached = points lookup in sheet called point - and so you can make any points you wish for any percent
Re: Formula to return a value based upon the partial contents of a celll
your welcome
Re: Nested IF statement with VLOOKUP, time format
have you checked the values from the vlookup and c16/i16 and checked they actually match
can you upload a sample
I suspect one is a text format rather than a time
Re: Trying to add 10 days to a Networkdays formula
try
=WORKDAY(p2,10)
Re: How to exclude zero values from the chart!
you may need VBA to sum the column and if 0 move onto the next month and then re-populate an area of the spreadsheet just for the graph
as you do not want to include a column on the chart range of all the data in that column is zero
Re: Formula to return a value based upon the partial contents of a celll
so I have used
=cell ie in B3 I put
=A3
so the date is transfered to that column
and then I have formated column B
custom
MMMM
to just display the month
see attached
Re: Formula to return a value based upon the partial contents of a celll
you could use the format and just display month MMMM
can you be more precise are you looking up a column of data or is it just one cell
perhaps attach a spreadsheet with examples and desired outcome
Month(Cell) will return the month number
Re: Find the Max Date of a chosen Month and give corresponding value
your welcome
Re: Find the Max Date of a chosen Month and give corresponding value
this will give you the lowest value nearest to or equal to the max date you have in cell named MAX
=VLOOKUP(MAX,B17:C49,2,TRUE)
and this will give you the maximum of the two values for that date
=MAX(VLOOKUP(C4,B17:C49,2,TRUE),VLOOKUP(C4,B17:D49,3,TRUE))
Re: Extracting Post Code and Locality
not a great solution , but may help while waiting for others to provide a better one
i found a formula which will extract the 5 digit number from a text string
and then modified to extract the number and all the text after the number
(LOOKUP(9.99E+307,LEFT(MID(A1,MATCH(TRUE,ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A1,"/","@")," ","@"),ROW(A$1:INDEX(A:A,LEN(A1))),5)+0),0),15),ROW($1:$15))+0),A1,1)
so a couple of issues
1) are all post code only 5 digitis ?
2) would any of the numbers at the start of the text house number ever be 5 numbers- if so this will not work
i'm sure others will offer a better solution
I then use find with the above formula to find the position of that text - add to a MID function so that the MID function will start with that character
and then use length - the position of that formula +1 to get the length of characters to extract from the start position of the 5 digit number
=MID(A1,FIND(LOOKUP(9.99E+307,LEFT(MID(A1,MATCH(TRUE,ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A1,"/","@")," ","@"),ROW(A$1:INDEX(A:A,LEN(A1))),5)+0),0),15),ROW($1:$15))+0),A1,1),LEN(A1)-FIND(LOOKUP(9.99E+307,LEFT(MID(A1,MATCH(TRUE,ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A1,"/","@")," ","@"),ROW(A$1:INDEX(A:A,LEN(A1))),5)+0),0),15),ROW($1:$15))+0),A1,1)+1)
use control+shift+enter to use as an array and get {} around the formula
Re: Distribute data evenly in 20%
i'm sure theres a better way, but my approach is
=IF(SUM($B$2:B2)/SUM($B$2:$B$18)<=0.2,5,IF(SUM($B$2:B2)/SUM($B$2:$B$18)<=0.4,4,IF(SUM($B$2:B2)/SUM($B$2:$B$18)<=0.6,3,IF(SUM($B$2:B2)/SUM($B$2:$B$18)<=0.8,2,1))))
Re: How to exclude zero values from the chart!
to stop a chart plotting zeros , replace with #N/A
you can use an IF
=IF(C7=0, #N/A, C7)
Re: Calculating hour average from 5min. timesteps
just change the group by on the pivot table to include Days and Hours - click on days and hours - both should be selcted and you should get
something simular to your example - only the date will be entered once in the column and then all the times will follow in next column until a day change
11/23/2008 08:00 78,58
............... 09:00 94,92
............... 10:00 11,11
11/24/2008 15:00 12,11
............... 16:00 11,34
11/25/2008 08:00 78,58
............... 09:00 94,92
............... 10:00 11,11
[/CODE]
Re: Use Vlookup to Output Product #'s and Quantities on Separate Sheet
Quote"Parts List" Every time you select a quanity for one of the parts, I want it to pop up on my parts list.
not quite clear on what you need to do
also no attached file
Re: Vlookup help
no match with the lookup or no value when it finds the lookup but no value to return
if no match - it will return a #n/a
and you can use
=IFERROR( the formula , error message)
so
=IFERROR(VLOOKUP(J17,$L$1:$M$44,2), 1)
would return a 1 if no lookup found
1 is not a null value by the way - a NULL is no data in a field which is also different to a blank cell just FYI
Re: Vlookup help
should work
try a index match
=index( M:M, (match( J17, L:L,0))
can you attach a spreadsheet - remember this is a public forum so no sensitive data
Re: Countif/Sum Product
seems to be working OK
ROW 14
G14 =1 header = 7 so 7
H14 =1 header =10 so 10 = 10+7 = 17
N14 = X an so i R14 that would be 1x2 = 2
S14 = sumproduct - 10x1 + 7x1 = 17 then + R14 =2 =19