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

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
116
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,

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


Book1
EFGHIJKLMNO
9Purchase CostPeriods to be Depreciated byDepreciation %Dep Chg in Months AppliedDep chg For Current yearB/fwdC/FB/fwd
107086025%12 708--708511.99
Sheet369
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
Hi,

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

EFGHIJKLMNO
Purchase CostPeriods to be Depreciated byDepreciation %Dep Chg in Months AppliedDep chg For Current year B/fwdC/FB/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
 
Upvote 0
ah I see if both columns F + G is blank, then it will bring up the error result.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You're welcome, welcome to the forum.

Just post back if you need further help.
 
Upvote 0
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.
 
Upvote 0
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
110
Sheet369
Cell Formulas
RangeFormula
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,"")))))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top