#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
change values based on cell | ubns | Excel | 1 | 05-21-2012 06:28 PM |
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 |
How to highlight the row based on cell value? | Learner7 | Excel | 1 | 12-11-2011 02:28 AM |
Cross-reference together with Mathtype formula is not working correct | Santa_Clause | Word | 2 | 02-02-2010 04:37 AM |