# Posts by etaf

• ## assigning points to percentages

Re: assigning points to percentages

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

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

• ## assigning points to percentages

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

• ## Formula to return a value based upon the partial contents of a celll

Re: Formula to return a value based upon the partial contents of a celll

• ## Nested IF statement with VLOOKUP, time format

Re: Nested IF statement with VLOOKUP, time format

have you checked the values from the vlookup and c16/i16 and checked they actually match

I suspect one is a text format rather than a time

• ## Trying to add 10 days to a Networkdays formula

Re: Trying to add 10 days to a Networkdays formula

try
=WORKDAY(p2,10)

• ## How to exclude zero values from the chart!

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

• ## Formula to return a value based upon the partial contents of a celll

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

• ## Formula to return a value based upon the partial contents of a celll

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

• ## Find the Max Date of a chosen Month and give corresponding value

Re: Find the Max Date of a chosen Month and give corresponding value

• ## Find the Max Date of a chosen Month and give corresponding value

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

• ## Extracting Post Code and Locality

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

• ## Distribute data evenly in 20%

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

• ## How to exclude zero values from the chart!

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)

• ## Calculating hour average from 5min. timesteps

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]

• ## Use Vlookup to Output Product #'s and Quantities on Separate Sheet

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

• ## Vlookup help

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

• ## Vlookup help

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

• ## Countif/Sum Product

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