VLOOKUP working on the calculation rather than the number shown

[Palacemad]

HI, I am sure this is a very quick question/answer,

In one cell(D38) I have a SUMIFS formula, the answer it is returning is 0.415. This number is actually a calculation of months in decimals. In the next cell (E38) I have a vlookup to return this figure in months. Cell D38 is rounded to 2 decimal places and is showing 0.42. The vlookup is still producing a returning value of 4 months, even though it should be 5 months. I have even tried using ROUNDUP within the VLOOKUP and this is still not having an impact.

I assumed the reason is due to the VLOOUP working on the actual calculation figure of 0.415, rather than the figure that is showing. However, I am unsure why it is not changing when the ROUNDUP formula is being used.

Any suggestions will be appreciated!

Thank you

 

[Palacemad]

Here is what is in C38:

=(SUMIFS(PIRA_Assessment1,PIRA_Baseline,">0",PIRA_Assessment1,">0")-SUMIFS(PIRA_Baseline,PIRA_Baseline,">0",PIRA_Assessment1,">0"))/$D$36

In the cell it is currently showing 0.415

This is a section from the decimal to age:

-0.58	-7
-0.5	-6
-0.42	-5
-0.33	-4
-0.25	-3
-0.17	-2
-0.08	-1
0	0
0.08	1
0.17	2
0.25	3
0.33	4
0.42	5
0.5	6
0.58	7
0.67	8
0.75	9
0.83	10
0.92	11
1	12
1.08	13
1.17	14
1.25	15
1.33	16

<colgroup><col width="112" style="width: 84pt;"><col width="147" style="width: 110pt;"></colgroup><tbody>
</tbody>

 

[AlanY]

it should works

Book1
	A	B	C	D	E	F
1	-0.58	-7		0.415	0.42	5 months
2	-0.5	-6
3	-0.42	-5
4	-0.33	-4
5	-0.25	-3
6	-0.17	-2
7	-0.08	-1
8	0	0
9	0.08	1
10	0.17	2
11	0.25	3
12	0.33	4
13	0.42	5
14	0.5	6
15	0.58	7
16	0.67	8
17	0.75	9
18	0.83	10
19	0.92	11
20	1	12
21	1.08	13
22	1.17	14
23	1.25	15
24	1.33	16
Sheet3

Cell Formulas
Range	Formula
E1	=ROUND(D1,2)
F1	=IF(ISBLANK(E1),"",ROUNDUP(VLOOKUP(E1,A1:B24,2,0),2)&" months")

 

[FormR]

To clarify too, the formula in E38 when using ROUNDUP is:

=IF(COUNTBLANK(C38)=1,"",ROUNDUP(VLOOKUP(C38,DecimalToAge,2),0)&" months")

I am fairly certain that is written correctly, happy to proved wrong though!

Hi, sounds to me like you want:

=IF(COUNTBLANK(C38)=1,"",VLOOKUP(ROUNDUP(C38,2),DecimalToAge,2)&" months")

 

[Palacemad]

FormR, that has worked correctly thank you. I understood that the ROUNDUP function would have to appear before the VLOOUP, not within it.

Alan, thank you too. I am unsure why your formula did not work, maybe because I didn't have the additional cells breaking down each step.

 

[AlanY]

glad that you got it sorted

 

[AlanY]

this might work as well, without the lookup table

=IF(ISBLANK(C38),"",ROUNDUP(C38*12,0)&" months")