Microsoft Office Forums Creating formula based on if data is correct in cell
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
08-02-2012, 08:36 AM
 MattMurdock Windows 7 64bit Office 2010 64bit Novice Join Date: Jul 2012 Posts: 5
Creating formula based on if data is correct in cell

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!

xpost- http://www.excelforum.com/excel-gene...html?p=2880458
Attached Files
 Testbook.xlsx (12.9 KB, 14 views)
#2
08-06-2012, 03:11 AM
 Venky62 Windows 7 64bit Office 2010 32bit Advanced Beginner Join Date: Jul 2012 Posts: 58

The logic you are using when calculating the % of insured cars for those months where one of the months does not have figures for Type A insurance is faulty. I have changed the formula so that the formula checks to see if there is a figure in the third month from the month of calculation, and if so it calculates Total insured cars/Total cars, otherwise it calculates Total insured cars/Total Cars of B Type and Forecast A. I guess this is what you were trying to do. For example, the formula cell for the month of September (J16), checks to see if there is a figure in the Type A cars cell of the month of November (L3). Since L3 is blank, it calculates total insured cars by total cars of B type (J8:L8)+total Forecast A (J15:L15). This formula assumes that if the Type A car field (Row 3) is populated in the third month, it is also populated in the first and second month. That means, if November column has a value in Row 3, the September and october months will also have a value in Row 3.

The worksheet with modified formula is attached.
Attached Files
 Testbook1.xlsx (13.1 KB, 1 views)

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post ubns Excel 1 05-21-2012 06:28 PM cklassen Excel 1 05-19-2012 01:25 AM ubns Excel Programming 1 05-07-2012 04:03 AM Learner7 Excel 1 12-11-2011 02:28 AM Santa_Clause Word 2 02-02-2010 04:37 AM

All times are GMT -7. The time now is 05:42 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft