double v,hLookUp

[DorianGrim]

Hi,

I have a hunch, a double vlookup is the solution to my problem. But I can't get it to work, cause of the troublesome A, B, columns. They must contain hours and minutes, where A are the hours and B are the minutes. Formating is regardless.

I have this table:

Book1.xlsx

I need to create a function, in a single cell, that will return my exact percentage, from today(), and column A, B. For example, if today is the 17th of May, and the current hour is 14:05, the percentage would be 9%. If it would be 20th of May, and the current hour is 15:45, the percentage would be 14%. Basically, every half and hour, the percentage in the cell should change.

Please help,
Thanks in advance,
Dorian

[NBVC]

Try something like:

=SUMIFS(INDEX($C$2:$AG$27,0,MATCH(TODAY(),$C$1:$AG$1,0)),$A$2:$A$27,HOUR(NOW()),$B$2:$B$27,"<="&MINUTE(NOW()))

formatted as percentage.

[jayajaya_4]

its an interesting challenge...to go for...do u have any criteria fields...in sheet ,really col A and B are creating problems

[mcmahobt]

A different method. See attached.

[samba_ravi]

=LOOKUP(MOD(NOW(),1),TIME(A2:A27,B2:B27,0),INDEX(C2:AG27,,MATCH(NOW(),C1:AG1)))
Try this
It will give a number in decimals change the cell format to "%"

[cbatrody]

=INDEX(Sheet1!$C$1:$AH$27,MATCH($B3,Sheet1!$C$1:$C$27,1),MATCH(C$2,Sheet1!$C$1:$AH$1,0))

[NBVC]

Actually, SUMIFS is not the right function...

try instead:


=LOOKUP(2,1/(($A$2:$A$27=HOUR(NOW()))*($B$2:$B$27<=MINUTE(NOW()))),INDEX($C$2:$AG$27,0,MATCH(TODAY(),$C$1:$AG$1,0)))

[DorianGrim]

Actually, SUMIFS is not the right function...

try instead:


=LOOKUP(2,1/(($A$2:$A$27=HOUR(NOW()))*($B$2:$B$27<=MINUTE(NOW()))),INDEX($C$2:$AG$27,0,MATCH(TODAY(),$C$1:$AG$1,0)))

Refferincing the cell where the formula is returns 0. Why would that happen?

[NBVC]

You have to format the cell as percentage.

[DorianGrim]

Refferincing the cell where the formula is returns 0. Why would that happen?

Nevermind, I had to percentage the cell.