Help - i'm stuck with the 'if' formula

[Novice_user]

Hi All,

I am very new to excel so bear with me please...!

So, I have a set of numbers in two columns

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl69"]a[/TD]
[TD="class: xl65, width: 64"]13/3[/TD]
[TD="class: xl69"]14/9[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl69"]b[/TD]
[TD="class: xl65, width: 64"]15/6[/TD]
[TD="class: xl69"]16/3[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl69"]c[/TD]
[TD="class: xl65, width: 64"]10/3[/TD]
[TD="class: xl69"]12/3[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl69"]d[/TD]
[TD="class: xl65, width: 64"]14/6[/TD]
[TD="class: xl69"]14/9[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl69"]e[/TD]
[TD="class: xl65, width: 64"]8/10[/TD]
[TD="class: xl69"]6/5[/TD]
[/TR]
[TR]
[TD="class: xl66"]6[/TD]
[TD="class: xl69"]f[/TD]
[TD="class: xl65, width: 64"]10/11[/TD]
[TD="class: xl69"]12/3[/TD]
[/TR]
[TR]
[TD="class: xl66"]7[/TD]
[TD="class: xl69"]g[/TD]
[TD="class: xl68, width: 64"]12/0[/TD]
[TD="class: xl69"]8/2[/TD]
[/TR]
[TR]
[TD="class: xl66"]8[/TD]
[TD="class: xl69"]h[/TD]
[TD="class: xl65, width: 64"]12/9[/TD]
[TD="class: xl69"]14/0[/TD]
[/TR]
[TR]
[TD="class: xl66"]9[/TD]
[TD="class: xl69"]i[/TD]
[TD="class: xl65, width: 64"]9/7[/TD]
[TD="class: xl69"]10/7[/TD]
[/TR]
</tbody>[/TABLE]

In column D I want to produce the following results - if the numbers in column C are greater than B then display the number in C, otherwise display column B

I tried using the IF formula =IF(c1>b1,c1,b1)

which was great & worked but when it got to name g the result returned was 8/2 which isn't right.

I looked further down and the results remain inconsistent throughout.

Help please!!

 

[Novice_user]

Hi,
forgot to mention the numbers are actually ages eg for name a - 13 years and 3 months is represented by 13/3 and 14 years and 9 months is represented by 14/9

Thanks

 

[Declamatory]

Hi, I think your data is formatted as text. If 14/9 is input to a cell normally Excel would convert that to 14-Sep.

 

[Declamatory]

If you want a cell to reflect months and years you need to right click on the cell → Format Cells →on the number tab click Custom Formats and in the box just below where it says Type insert the following: 00 "Year(s)," 00 "Month(s)" then click OK

Now when you input 1409 into the cells the cell will read 14 Year(s), 09 Month(s). Crucially your formula will now work.

 

[Novice_user]

Hello,

The data comes already to me in the format 12/0 (for yrs/m) do you mean I have to change it all to 12 years 0 months before I do the IF formula? If so how can I do this quickly to the whole column?

Thanks for the help.

 

[Mathias Pedersen Heinze]

So, basically what you want is to return the largest value of columns B and C in column D. One way to do this would be to substitute the / with a . and thus turn the text values into decimal numbers and then in turn use the MAX function to get the biggest. Paste this formula in D1, close it with Ctrl + Shift + Enter and double click to send it down.

=MAX(VALUE(SUBSTITUTE(B1:C1,"/",".")))

While this will return the biggest number in decimal formatting, you can append the formula to return the biggest value in the original format. Use this formula instead.

=INDEX(B1:C1,,MATCH(MAX(VALUE(SUBSTITUTE(B1:C1,"/","."))),VALUE(SUBSTITUTE(B1:C1,"/",".")),0))

Remember to close them both using Ctrl + Shift + Enter since they are array formulas.

 

[Novice_user]

Thank you so much - it's a good workaround and saves me going through 1500 data records manually.


Boss gave me a whole load of spreadsheets to do & I have no prior experience of excel at all.