PDA

View Full Version : Creating formula based on if data is correct in cell

MattMurdock
2012-08-02, 05:12 PM
Hi everyone- I'm having a problem with a formula for work. Attached is a simplified version of my problem.

My job is to find the %s of all insured cars, based on two different types of insurance. However, one insurance (type A) runs out before the other, and when that happens we use a forecasted/projected amount. I created a simple IF formula to show the insured % amounts for the next three months. If theres a value in type A insurance, use total insured cars/total cars. If there isn't a value in Type A, use total insured cars/total cars + forecast A. This formula breaks down in the months of Sept and Oct (if you look at the comments in the attached wrkbook).

Thanks for any and all help!

CheshireCat
2012-08-02, 06:36 PM
Hi MattMurdock,
In cell J16, try this:

=IF(OR(J3="",K3="",L3=""),SUM(J14:L14)/SUM(J13:L13,J15:L15),SUM(J14:L14)/SUM(J13:L13))

MattMurdock
2012-08-02, 08:56 PM
Hi MattMurdock,
In cell J16, try this:

=IF(OR(J3="",K3="",L3=""),SUM(J14:L14)/SUM(J13:L13,J15:L15),SUM(J14:L14)/SUM(J13:L13))

Great thank you I think this should work!