Blank cell in formula

[TGP]

Hello All,

I suspect this is a very simple question to some of you, but I simply don't know how to revise the formula to get the result i want.

In the attached spreadsheet if cell B9 is left blank then range D10:K10 returns zero. How do I revise the formula in cells D10:K10 so that if B9 is emply the cells F10:K10 returns the answer 5,000?

Thanks to anyone who can provide some assistance.

[Trebor76]

Hi TGP,

See if the revised formulas in the attached are what you're after.

Regards,

Robert

[TGP]

Hi Robert,

Partially what I'm after but in your example when I enter a date into B9 then the cells return zero.

What I need is for the cells representing the dates between the start date and the end date to return A7 does this make sense?

Thanks,
TGP

[TGP]

Hi TGP,

See if the revised formulas in the attached are what you're after.

Regards,

Robert

Hello Robert,

I have revised your formula and it now works as i had hoped for (see attached), but I don't understand the first part of your formula?

=IF(COLUMN()>=6,

What is this asking for? Why is the 6 relevant? Confused in Sydney?

Thanks you for your help!

[teylyn]

Maybe

=IF(OR(AND(D9>=$A$9,D9<=$B$9),AND(D9>=$A$9,$B$9="")),$A$7,0)

copied across

[TGP]

Thanks Teylyn. Much appreciated!!

[TGP]

Hi Teylyn,

Not sure you are still there, but I have a quick question.

In your solution if there is a blank start date the cells return A7, how can I get this to return zero if there is no start date entered?

Cheers,
TGP

[teylyn]

something like this, maybe

=IF(OR(AND(D9>=$A$9,D9<=$B$9),AND($A$9<>"",D9>=$A$9,$B$9="")),$A$7,0)

[TGP]

Genius! Thank you for getting back to me.

[Trebor76]

Hi TGP,

Just to clarify my formula, in your original post you said that you wanted the formula to kick in from Column F which, formula wise, is Column 6.

I'm glad that teylyn was able to provide a workable solution.

Regards,

Robert

[sureshp.rpt]

Hi ,
I want use the Nested if more 10 times , but in excel it was allow to use upto 7 times only . My condition are follows :

Cell value < 10 "Group1"
Cell value > 10 & Cell value <=20 ,"Group2"
Cell value > 20 & Cell value <=30 ,"Group3"
Cell value > 30 & Cell value <=40 ,"Group4"
Cell value > 40 & Cell value <=50 ,"Group5"
Cell value > 50 & Cell value <=60 ,"Group6"
Cell value > 60 & Cell value <=70 ,"Group7"
Cell value > 70 & Cell value <=80 ,"Group8"
Cell value > 80 & Cell value <=90 ,"Group9"
Cell value > 100 ,"Group11"

But i tried this is condition in excel 2007 , but it fails . Kindly give the solution for my problems or kindly send the procedure to do in macros.

THanks in advance

[teylyn]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.