Problem creating an 'If' formula

[Wirral Wizard]

I am struggling to create a formula for the following scenario.

I have two rows of data and there is one entry in each column. Most of the data is numerical but there is some data that is preceded by an 'F' (e.g F7).

I want to create a formula that ignores any data that is not purely numerical in the first column (i.e any which includes an 'F') and finds the first column entry that contains a number. It then has to compare a total for the row that the entry is contained in with the total for the other row and return a 1 if the total is bigger or a 0 if the total is smaller.

Example data set:

F7 - 12 - 8 - 56 Total: 83 (includes the 7 from F7)
- 5 - 36 - 2 - Total: 43

I have tried an 'IF' formula that is supposed to look for values >0 but it still includes 'F7' as greater than 0. In the above scenario the correct result should be 0 as the first valid entry is 5 and the total for that row is smaller than the row above it. But I am getting a result of 1 as the formula thinks F7 is >0.

I'm sure there's a simple solution but I'm struggling to find it!

Thanks in anticipation of any help you can give.

WW

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Wirral Wizard]

Thanks Dave. Hope I've done this right!

See attachment.

[darkyam]

Try =SUM(IF(B2:AE2<>"",SUBSTITUTE(B2:AE2,"F","")+0)), confirmed with Control+Shift+Enter

[DonkeyOte]

On the off chance I've understood:

Please Login or Register  to view this content.

above should be confirmed with CTRL + SHIFT + ENTER

(if correct, above can be copied to AL4 re: rows 4 & 5)

[Wirral Wizard]

And I thought it was a simple solution! That's brilliant DonkeyOte, thank you very much for your help.

I have done some more work on my spreadsheet this evening and have modified the formula for some analysis that I am doing...and just come across a problem that I don't understand.

See the attached spreadsheet, rows 14 and 15 and compare with rows 17 and 18..

The modified formula can be seen in cell AK14. I have used a simple 'IF' formula in cell AL14 but I don't understand why it is returning a value of "0" when it should be "1".

It returns the correct value if I change the value in Cell C17 to 4 from F4, even though the result in cell AK17 is still the same.

There will be several rows where there are sequences of entries preceded by 'F' before the first numerical entry so could do with finding a solution to this.

Thanks for all your help so far.

WW

[DonkeyOte]

The issue is down to rounding.

If you view AK14 to 15 decimals you will find the value is not 1 but 0.999999999999996 thus the AK14=1 does not hold True in the AND.

Needless to say this is confusing when you consider that AK17, which seemingly uses the exact same formula, returns 1.000000000000000 ... but I'm afraid that's just the way it is - for more info. see: http://support.microsoft.com/kb/78113

To avoid rounding issues round your data

=ROUND(10*MOD(MIN(IF(ISNUMBER(B14:AE15),COLUMN(B14:AE14)+(ROW(AE14:AE15)-ROW(AE14)+1)/10)),1),0)
confirmed with CTRL + SHIFT + ENTER

(in general terms you would modify the num_digits variable per decimal significance - here that would be 0 - ie whole number)

[Wirral Wizard]

Excellent! Thank you once more Mr.Ote!

I've just got one final piece of the jigsaw if you would be able to help with this too.

On the updated spreadsheet attached I am now looking at Rows 17 and 18. Cell AK17 now correctly identifies that the first valid numerical entry is in the 1st Row. Cell AL17 then counts if the entry is in the first row and the total in the first row > the total in the second row.

I now want a count in Cell AM17 if the first numerical entry is in the first row, is greater than 19 and the total in the first row > the total in the second row.

What would be the best way to do this?

[baby_mic]

Hello,

I also have a problem with an "if" formula. As you can see in attached sample, i have a range of months, and I extracted the year using a MID formula. However, in our company the Fiscal Year begins in October, so 200710 should already be 08, 200810 - 09, and so on. How could I introduce an "if" formula (or something else), so that I can turn months into FY?

Many thanks in advance for your help.

Later edit: Sorry darkyam for posting in someone else's thread, I did read the rules when I registered, I admit I didn't think this one through. Pregnancy really does make one hare-brained!

[darkyam]

Baby_mic, please take a moment to read the rules, particularly #2, and then start your own thread. Thanks.

[DonkeyOte]

I now want a count in Cell AM17 if the first numerical entry is in the first row, is greater than 19 and the total in the first row > the total in the second row.

Perhaps then:

=0+IF(AK17*AL17,INDEX(B17:AE17,MATCH(TRUE,INDEX(ISNUMBER(B17:AE17),0),0))>19))

[Wirral Wizard]

DonkeyOte...thank you so much for all your help. You were absolutely brilliant!

[Wirral Wizard]

DonkeyOte, could you help me with one more formula for my spreadsheet? I have tried to do it myself using some of the ideas you helped me with before but I'm just not getting it right!

In the spreadsheet attached to my post on 18th March at 11.17am, I now want to put a formula in, say, cell AM14 that puts the value of the first number that appears in one of the columns, irrespective of which row it appears in.

For example, in the range B14:AE15, it should return the value of '1' for cell G14 and in the range B4:AE5 it should return the value of '5' for Cell C5.

Thanks again for your help.

[DonkeyOte]

Perhaps:

AM14:
=ROUND(1000000*MOD(MIN(IF(ISNUMBER(B14:AE15),COLUMN(B14:AE15)+(B14:AE15/1000000))),1),0)
confirmed with CTRL + SHIFT + ENTER

above assumes numeric values are always positive, whole and less than 1,000,000

[Wirral Wizard]

Awesome DO, just awesome! Thank you once again for your help, I am not worthy!

[DonkeyOte]

In hindsight it's worth noting that the prior suggestion would be open to error should 2 numbers appear in the first column containing a number.
The present formula would return the lower of the 2 values as opposed to that appearing in the first row (if greater than value in row below).

Let me know if you need to account for this.