Formula shows #value! error when source cell is empty

[tstowe]

Please take a look at the attached and help me with several issues.

1. I want the totals in rows 7-11 to calculate even when a row does not have a SSN for a soldier keyed (such as rows 5-6). Take a look at cells AA5 and AB5 (and across to the right) where I have a #value! error and a small value, respectively. These values are interfering with the calculations in rows 7-11 and I cannot figure out how to make those cells work properly so that they do not have the error and remain blank when no SSN is keyed.

2. I need to explain column Z (the 1199 field). It is the sum of the soldiers Basic Allowance for Subsistence (BAS) (on sheet Paydata) and the soldiers BAH (on sheet BAH Table TN). Additionally, with it being the new year in JAN, the BAH and BAS gains a percentage increase which is keyed in B3 and C3, respectively.

You will notice that I have cells E3-H3 performing the individual calculations and in cell Z3 the sum(e3,h3) formula. I tried using the formula in cell Q3 to perform these calculations and the result was incorrect. Here is what I tried in cell Z3 that resulted in several hundred dollars more than the actual amount:

=IF(ISBLANK(D3),"",O3+(O3*$B$3))+(IF(LEFT(J3)="E",PayData!$D$30,IF(OR(LEFT(J3)={"O","W"}),PayData!$D$29,""))+(IF(LEFT(J3)="E",PayData!$D$30,IF(OR(LEFT(J3)={"O","W"}),PayData!$D$29,""))*$C$3))

3. You will notice on sheet PERS OCT 2012 that column M (TIS = Time In Service) is determining the number of years that a soldier has been in the military. This is important to determine his base pay amount (on sheet PayData) based upon his rank. Column N (Payrate), looks at column M and references range [paytable1] to determine the amount.

First, is there a better method (formula) to determine the soldiers base pay rate? If so, please share your thoughts as this rate is then pulled into the main sheet PTAE FY 13 Budget in cell P3 and Y3.

If not, I want to know a better, more functional method, for populating the months of NOV, DEC, and FEB through SEP with the data calculated in OCT and JAN. To explain, OCT is the new fiscal year and my budget planning begins there and will be the same for NOV and DEC. If a pay and allowances increase is passed by Congress, then in JAN those base amounts have to be recalculated with the percentages keyed into cells A3, B3,and C3. Then, the cells for FEB through SEP have to reflect the new amounts shown for JAN.

4. One final piece requires you to look at sheet PayData. You will notice that every two years a soldier will move into a new base pay amount and the spreadsheet currently does not take that into account. How do I make each individual month look at the soldiers TIS and if, at that month, the soldier moves into a new pay bracket make the new calculations? Then, every subsequent month will have to show the new amounts and not the amounts calculated in OCT and JAN alone.

So, lets eat this elephant one bite at a time... where would any of you smart people like to begin? First and foremost, thanks for your help.

Tony
=======================

What I would like to have is:

1. Cell Z to perform all of the calculations without the need for columns E-H.
2. When a soldiers SSN is not keyed in column I, I want the formulas across the sheet to remain empty (while keeping the formulas) so that the sheet totals can calculate.

[jeffreybrown]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[Tsjallie]

Hi,
a quick look reveals that in the formula in cell F5 on sheet PTAE FY 13 Budget you are trying to add the value of cell G5 to string. That gives a #Value-error in F5 and in all the cells which directly or indirectly refer to cell F5.
I do not know what this formula in F5 is doing so I can't correct it for you, but I gues you can do that yourself.
BTW in the formula menu you can select "Evaluate formula". That's very helpfull with dubugging formulas.

[Richard Buttrey]

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc