Formula result not displayin in cell
 

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?

Formula result not displayin in cell
 

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(--(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

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

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

Yong Heng
Singapore

"John G - HTW" wrote:

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?

Formula result not displayin in cell
 

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(--(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

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

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

"Yong Heng" wrote:

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(--(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

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

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

Yong Heng
Singapore

"John G - HTW" wrote:

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?

Formula result not displayin in cell
 

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


"Yong Heng" wrote:

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(--(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

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

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

"Yong Heng" wrote:

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(--(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

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

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

Yong Heng
Singapore

"John G - HTW" wrote:

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?

Formula result not displayin in cell
 

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.


"Yong Heng" wrote:

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(--(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

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

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

"Yong Heng" wrote:

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(--(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

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

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

Yong Heng
Singapore

"John G - HTW" wrote:

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?