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

#### Novice_user

##### New Member
Hi All,

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

So, I have a set of numbers in two columns

 A B C 1 a 13/3 14/9 2 b 15/6 16/3 3 c 10/3 12/3 4 d 14/6 14/9 5 e 8/10 6/5 6 f 10/11 12/3 7 g 12/0 8/2 8 h 12/9 14/0 9 i 9/7 10/7

<tbody>
</tbody>

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.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

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.

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.

Last edited:
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.

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.

Code:
``=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.

Code:
``=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.

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.

Replies
4
Views
174
Replies
3
Views
130
Replies
3
Views
113
Replies
0
Views
162
Replies
6
Views
340

1,196,266
Messages
6,014,327
Members
441,816
Latest member
Klingon1960

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back