Problem creating an 'If' formula

**Wirral Wizard** · 03-17-2010, 04:23 PM

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** · 03-18-2010, 03:51 AM

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** · 03-18-2010, 06:17 AM

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** · 03-18-2010, 08:48 AM

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!