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:

[TABLE="width: 259"]
<colgroup><col width="112" style="width: 84pt;"><col width="147" style="width: 110pt;"></colgroup><tbody>[TR]
[TD="class: xl64, width: 112, align: center"]-0.58[/TD]
[TD="class: xl63, width: 147, align: center"]-7[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]-0.5[/TD]
[TD="class: xl63, align: center"]-6[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]-0.42[/TD]
[TD="class: xl63, align: center"]-5[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]-0.33[/TD]
[TD="class: xl63, align: center"]-4[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]-0.25[/TD]
[TD="class: xl63, align: center"]-3[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]-0.17[/TD]
[TD="class: xl63, align: center"]-2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]-0.08[/TD]
[TD="class: xl63, align: center"]-1[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0[/TD]
[TD="class: xl63, align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.08[/TD]
[TD="class: xl63, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.17[/TD]
[TD="class: xl63, align: center"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.25[/TD]
[TD="class: xl63, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.33[/TD]
[TD="class: xl63, align: center"]4[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.42[/TD]
[TD="class: xl63, align: center"]5[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.5[/TD]
[TD="class: xl63, align: center"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.58[/TD]
[TD="class: xl63, align: center"]7[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.67[/TD]
[TD="class: xl63, align: center"]8[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.75[/TD]
[TD="class: xl63, align: center"]9[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.83[/TD]
[TD="class: xl63, align: center"]10[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]0.92[/TD]
[TD="class: xl63, align: center"]11[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]1[/TD]
[TD="class: xl63, align: center"]12[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]1.08[/TD]
[TD="class: xl63, align: center"]13[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]1.17[/TD]
[TD="class: xl63, align: center"]14[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]1.25[/TD]
[TD="class: xl63, align: center"]15[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]1.33[/TD]
[TD="class: xl63, align: center"]16[/TD]
[/TR]
</tbody>[/TABLE]

 

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