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

Novice_user

New Member
Joined
Nov 29, 2016
Messages
9
Hi All,

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

So, I have a set of numbers in two columns

ABC
1a13/314/9
2b15/616/3
3c10/312/3
4d14/614/9
5e8/106/5
6f10/1112/3
7g12/08/2
8h12/914/0
9i9/710/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.

Help please!!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top