Excel 2007 : Help with an IF function

[derivative_x]

Hi,

I'm trying to build a template in excel using an IF function (assuming that's what I need to use...if there's anything different, I'm open to suggestions.

In column A, I have a dollar amount
In column B, I have an allocation drop-down list build in with the options of Annually, Semiannually, Quarterly, Monthly, and Custom
In columns C-N, I have Jan through Dec where I can show how much I'm spending per month

My problem is:
I want to be able to drop down monthly and have the dollar amount in column A divided by 12 and input in each month,
custom: I want to be able to enter in the data as I want
(here's where it gets hairy)...
Semiannually, divide by 2 and end up in Jan and June only
Quarterly, divide by 4 then input in Jan, Apr, July, Oct only

Is that even possible using IF or do I need to consult a macro builder instead?

[JBeaucaire]

A formula can do all this. Post up your workbook with 3-4 example rows of data manually depicting the whole scenario properly, we can show you exactly from there.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.


Don't expect "Custom" to be a simple addition.

[derivative_x]

Hello,

Sorry for the delay....

I've attached a copy of the workbook for additional support. If needed custom can be just left out...and I can provide a note on the workbook that directs people to just enter in the data on a non-automated basis. For custom, I just want it to do nothing so I know to just put in the numbers myself

thanks,
Paul

[derivative_x]

Here's a better picture of the information

thanks for your help,
Paul

[JBeaucaire]

This is really a series of formulas. Each on appropriate for each choice in column B. In C7, the various formulas would be:

Monthly =$A7/12
Quarterly =IF(ISNUMBER(SEARCH(C$6,"JANAPRJULYOCT")), $A7/4, "")
Semiannually =IF(ISNUMBER(SEARCH(C$6,"JUNDEC")), $A7/2, "")
Annually =IF(C$6="Jan", $A7, "")
Custom ="-"


Now, we can use a CHOOSE() formula put all those formulas in an array, then we can use a MATCH() function against the choice made in column B to select the correct "position" based on your named range Allocate.

=MATCH($B7,Allocate,0)

Putting it altogether:

=CHOOSE(MATCH($B7,Allocate,0), IF(C$6="Jan", $A7, ""), IF(ISNUMBER(SEARCH(C$6,"JUNDEC")), $A7/2, ""), IF(ISNUMBER(SEARCH(C$6,"JANAPRJULYOCT")), $A7/4, ""), $A7/12, "-")


Last addition, let's make sure NOTHING happens at all if either A or B is blank. Put this formula in C7 , then copy that cell down and across the entire purple section:

=IF(OR($A7=0,$B7=""), "", CHOOSE(MATCH($B7,Allocate,0), IF(C$6="Jan", $A7, ""), IF(ISNUMBER(SEARCH(C$6,"JUNDEC")), $A7/2, ""), IF(ISNUMBER(SEARCH(C$6,"JANAPRJULYOCT")), $A7/4, ""), $A7/12, "-"))

[derivative_x]

I cannot believe it! It works! I feel like Tom Hanks in Cast Away when he made fire! Haha

Thank you sooooooooooooooooooooooooooo much

-Paul

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.