Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-02-2012, 08:36 AM
MattMurdock MattMurdock is offline Creating formula based on if data is correct in cell Windows 7 64bit Creating formula based on if data is correct in cell Office 2010 64bit
Novice
Creating formula based on if data is correct in cell
 
Join Date: Jul 2012
Posts: 5
MattMurdock is on a distinguished road
Default 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
File Type: xlsx Testbook.xlsx (12.9 KB, 20 views)
Reply With Quote
  #2  
Old 08-06-2012, 03:11 AM
Venky62 Venky62 is offline Creating formula based on if data is correct in cell Windows 7 64bit Creating formula based on if data is correct in cell Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2012
Posts: 58
Venky62 is on a distinguished road
Default

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
File Type: xlsx Testbook1.xlsx (13.1 KB, 7 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
change values based on cell ubns Excel 1 05-21-2012 06:28 PM
Creating formula based on if data is correct in cell Can't get formula to recognize correct data cklassen Excel 1 05-19-2012 01:25 AM
Macro based on cell value ubns Excel Programming 1 05-07-2012 04:03 AM
Creating formula based on if data is correct in cell How to highlight the row based on cell value? Learner7 Excel 1 12-11-2011 02:28 AM
Creating formula based on if data is correct in cell Cross-reference together with Mathtype formula is not working correct Santa_Clause Word 2 02-02-2010 04:37 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:50 AM.


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