Formula result not displayin in cell

[John G - HTW]

I've just created a nested/embedded logical formula, which appears okay
(i.e., with out errors), and the Function Arguments dialogue box displays an
answer. However, the cell still displays zero and I don't understand why.

If it's of any assistance, I have included the formula below:
=ROUND(IF(AND(G34="Not Applicable",G35="No Allowance Made"),G36,IF(G34="Not
Applicable",G36+G35,IF(G35="No Allowance Made",G36-G34,G36+G35-G34))),0)

I've checked my formatting and this appears okay. Does anyone have any
explanation as to why my result is not displaying?

 

[Yong Heng]

Hi,

It'll be helpful if u could provide some sample data.

However, looking at your formula, you are trying to perform arithmetic
operations on G35 which could have a value of "No Allowance Made"

You may be better off using sumproduct():

A B C D E F
"Not Applicable" "No Allowance Made"
1 2 3 TRUE TRUE
4 5 6 TRUE
7 8 9 TRUE

not applicable and no allowance made
=SUMPRODUCT(--(D24)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

not applicable ONLY
=SUMPRODUCT(--(D24)*NOT(E2:E4),A2:A4,B2:B4)
result = 20 (4x5x6)

no allowance made ONLY
=SUMPRODUCT(--NOT(D24)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 504 (7x8x9)

Yong Heng
Singapore

 

[Yong Heng]

minor correction, it should be:

A B C D E F
"Not Applicable" "No Allowance Made"
1 2 3 TRUE TRUE
4 5 6 TRUE
7 8 9 TRUE

not applicable and no allowance made
=SUMPRODUCT(--(D24)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

not applicable ONLY
=SUMPRODUCT(--(D24)*NOT(E2:E4),A2:A4,B2:B4,C2:C4)
result = 120 (4x5x6)

no allowance made ONLY
=SUMPRODUCT(--NOT(D24)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 504 (7x8x9)

 

[Yong Heng]

the spaces are not showing, the table in my example should look like this:
A B C D E F
<space> <space> <space> "Not Applicable" "No Allowance Made"
1 2 3 TRUE TRUE
4 5 6 TRUE <space>
7 8 9 <space> TRUE

 

[John G - HTW]

Hi Yong Heng

Thank you for your quick response.

This method of communication doesn't allow for attachments so I have taken a
section of the worksheet I'm working on and pasted it below:

Gross Income #VALUE! psmpa
$0
Plus Other Income Streams Not Applicable
Less Permanent Vacancy Allowances 0.00% No Allowance Made
Gross Rental Income
$116,658
Rates & Fire Levy $5,000
Other Outgoings $0
Land Tax $313
Insurance $3,024
Repairs & Maintenance $4,321
Management $4,000
Total Outgoings / Net Income $16,658 $100,000
Per square metre per annum $18.19
As a % of Gross Income (after PVA) 14.28%

The task I'm performing is a stepwise calculation from the bottom up. I've
started with the Net Income at $100,000, then added the costs in the left
hand column, to yield the Gross Rental Income. Everything is simple and easy
up to this point. However, I want to get from Gross Rental Income to Gross
Income. Because the above cells are of zero values and contain a logical
argument which displays the comments "Not Applicable" and "No Allowance
Made", I cannot merely add the Permanent Vacancy Allowance and subtract the
Other Income Streams to yield the Gross Income, hence the need of my formula.
Like I indicated previously, the function argument dialogue box does display
an answer, which in this instance is $116,658, but only $0 appears in the
cell.

Thanks again for your help.

John G.