IF AND NOT ISBLANK ISBLANK and do a sum if conditions are met - meltdown

[Dave01]

Hi everyone, I'm working on a depreciation spreadsheet, and Im usually really good at formulas, but I just cant get this one to work. Some reason I cant paste pictures, so Im going to write out my excel layout, hope it makes sense, the formula goes in Column I

The formula applies to Row 10


Columns

E F G H I J M 0
[TABLE="width: 851"]
<tbody>[TR]
[TD]Purchase Cost[/TD]
[TD]Periods to be Depreciated by[/TD]
[TD]Depreciation %[/TD]
[TD]Dep Chg in Months Applied[/TD]
[TD]Dep chg For Current year [/TD]
[TD] B/fwd[/TD]
[TD][/TD]
[TD][/TD]
[TD]C/F[/TD]
[TD][/TD]
[TD]B/fwd[/TD]
[/TR]
[TR]
[TD]£708.00[/TD]
[TD]60[/TD]
[TD]25%[/TD]
[TD]12[/TD]
[TD](FORMULA)[/TD]
[TD] £708.00[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 708.00[/TD]
[TD][/TD]
[TD] 511.99[/TD]
[/TR]
</tbody>[/TABLE]

This is my current formula -

=IF(AND(F10="",G10=""),"Error",IF(AND(F10="",NOT(ISBLANK(G10))),J10-O10)*0.25),IF(AND(G10="",NOT(ISBLANK(F10))),J10/F10)*H10))




So this is how it is supposed to work.

IF columns F & G are not blank this returns an Error.
If columns F10 is blank, but column G10 has a value (as a %) then it will do the calculation, 708 - 511.99 x 25%. The answer should be £49.
If columns F10 is has a value, but column G10 has a blank then it will do the calculation 708 / 60 * 12. This should be 142.


The formula as is posted, wont even execute because of the brackets here *0.25) and here *H10))


This does work

IF(AND(F10="",G10=""),"Error",IF(AND(F10="",NOT(ISBLANK(G10))),J10-O10)*0.25) - and gives me the answer of 49.

but as soon as I introduce the last bit - IF(AND(G10="",NOT(ISBLANK(F10))),J10/F10)*H10 for the 60 periods, it will give a result of 0.00 with the 25% still in there without the 60 entered. Removing the 25% and entering 60 in column F10 will also give a 0.00 result.


I do want it to go one step further and depending on the method - Reducing or fixed at a standard rate, by entering a R or F, but I need to get the above working done first

I really would appreciate some help to get this working as is should.

Many thanks

Dave.

 

[jtakw]

Hi,

You just have midsplaced/missing brackets in your formula, corrected below:

Book1
	E	F	G	H	I	J	K	L	M	N	O
9	Purchase Cost	Periods to be Depreciated by	Depreciation %	Dep Chg in Months Applied	Dep chg For Current year	B/fwd			C/F		B/fwd
10	708	60	25%	12		708	-	-	708		511.99
Sheet369

Cell Formulas
Range	Formula
I10	=IF(AND(F10="",G10=""),"Error",IF(AND(F10="",NOT(ISBLANK(G10))),(J10-O10)*0.25,IF(AND(G10="",NOT(ISBLANK(F10))),J10/F10*H10,"")))

When Both F10 and G10 has Value, Formula results Blank.

 

[Dave01]

Hi,

You just have midsplaced/missing brackets in your formula, corrected below:

	E	F	G	H	I	J	K	L	M	N	O
Purchase Cost	Periods to be Depreciated by	Depreciation %	Dep Chg in Months Applied	Dep chg For Current year	B/fwd	C/F	B/fwd
	-	-

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]708[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]708[/TD]

[TD="align: right"]708[/TD]
[TD="align: right"][/TD]
[TD="align: right"]511.99[/TD]

</tbody>

Sheet369​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I10[/TH]
[TD="align: left"]=IF(AND(F10="",G10=""),"Error",IF(AND(F10="",NOT(ISBLANK(G10))),(J10-O10)*0.25,IF(AND(G10="",NOT(ISBLANK(F10))),J10/F10*H10,"")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



When Both F10 and G10 has Value, Formula results Blank.

Thank you it works, I was going round in circles and just could not spot it. It seems to ignore the first bit though, because if both F & G has a value, it comes up blank, instead of error,
its not an essential though, as no calculation will go through if there is not a value in I.

Great Job Thanks

 

[Dave01]

ah I see if both columns F + G is blank, then it will bring up the error result.

 

[jtakw]

Yes, your OP formula says when F and G is Blank, result "ERROR", yet your written description was the Opposite.
Just change the ="" to <>"" if you want it the other way around.

 

[Dave01]

Yes, your OP formula says when F and G is Blank, result "ERROR", yet your written description was the Opposite.
Just change the ="" to <>"" if you want it the other way around.

yes, I realised that after you helped me getting working, and the description was how I would like it. Ive stared at these formulas all day, so when I close my eyes, im still seeing excel.
time to call it a day I think. Thanks for all your help, you really helped me out.

 

[jtakw]

You're welcome, welcome to the forum.

Just post back if you need further help.

 

[Dave01]

You're welcome, welcome to the forum.

Just post back if you need further help.

actually I could do with solving the final bit of the formula which I tried to added on only only got 1 of the 2 conditions working. The Bit in red is my addition. the formula gets accepted in excel. however when I type SL it returns a false. If I type a RB it calculates it correctly. the RB is on a reducing balance basis, so it will take the cost of asset less Depreciation x 25 %

SL - straight line should take the cost of asset x 25%, but this results in False. the answer should be 177.


=IF(ISBLANK(A10),0,IF(AND(G10<>"",H10<>""),"ERROR",IF(F10="RB",IF(AND(G10="",NOT(ISBLANK(H10))),(K10-P10)*H10,IF(F10="SL",IF(AND(G10="",NOT(ISBLANK(H10))),K10*H10,IF(AND(H10="",NOT(ISBLANK(G10))),K10/G10*I10,"")))))))


there are only one of two entries SL or RB. I dont understand why it takes one and not the other as the SL one I slotted in, and did play with the () but to not effect.

thanks again.

 

[jtakw]

I don't have any data from your posted sample for A, K, and P, and I didn't mock up any values for them because it seems your data setup has changed (your formula cell references in Post # 8 compared to your OP).

Also, I'm assuming this formula No longer resides in I10, since your formula uses I10 for calculations.

So, just going thru your formula's logic, see if this works for you:

Book1
	I
11	0
Sheet369

Cell Formulas
Range	Formula
I11	=IF(ISBLANK(A10),0,IF(AND(G10<>"",H10<>""),"ERROR",IF(AND(F10="RB",G10="",NOT(ISBLANK(H10))),(K10-P10)*H10,IF(AND(F10="SL",G10="",NOT(ISBLANK(H10))),K10*H10,IF(AND(H10="",NOT(ISBLANK(G10))),K10/G10*I10,"")))))

 

[Dave01]

I don't have any data from your posted sample for A, K, and P, and I didn't mock up any values for them because it seems your data setup has changed (your formula cell references in Post # 8 compared to your OP).

Also, I'm assuming this formula No longer resides in I10, since your formula uses I10 for calculations.

So, just going thru your formula's logic, see if this works for you:

	I

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]11[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet369​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I11[/TH]
[TD="align: left"]=IF(ISBLANK(A10),0,IF(AND(G10<>"",H10<>""),"ERROR",IF(AND(F10="RB",G10="",NOT(ISBLANK(H10))),(K10-P10)*H10,IF(AND(F10="SL",G10="",NOT(ISBLANK(H10))),K10*H10,IF(AND(H10="",NOT(ISBLANK(G10))),K10/G10*I10,"")))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

yes sorry I should have given you the full info. Column A is just a date when the asset was purchased, so if there is no date, there is no asset, therefor no calculation so returns a blank value. Columns did move as I had to insert the Method of Depreciation.


Its fantastic, yes it works great, cant thank you enough for resolving this for me. WOW your like the supreme commander of excel !!

Have a great weekend,

Dave.