Previous 1 2 15 Replies Latest reply: Aug 31, 2013 2:52 AM by Yellowbox
Level 1 (0 points)

COUNT and AVERAGE are reading zeros returned by IF function in another cell, but not other numbers. I'm puzzled after lots of study. Does this mean anything to someone more Numbers savvy than I am?

iMac, iOS 6.1.4
• Level 5 (7,855 points)

Hi Peter,

What is the formula related to the IF function? To what data does the IF function refer?

Regards,

Ian.

• Level 1 (35 points)

Could you send the whole formula over please

• Level 1 (0 points)

Yes, here is the original formula:

=IF(NOT(ISBLANK(A)),IF(E3="Optimal",SUBSTITUTE(E3,E3,0),IF(E3="Normal",SUBSTITUT E(E3,E3,1) ,IF(E3="High-normal",SUBSTITUTE(E3,E3,2),IF(E3="mild",SUBSTITUTE(E3,E3,3),IF(E3 ="Moderate",SUBSTITUTE(E3,E3,4),IF(E3="Severe",SUBSTITUTE(E3,E3,5),IF(E3=TRUE,SU BSTITUTE(E3,E3,3)))))))),"")

But I've tried different ones, some quite simple and straight forward, including =SUM(F\$2:F8) right underneath the range F2:F8)

• Level 1 (0 points)

Yes, here is the original formula:

=IF(NOT(ISBLANK(A)),IF(E3="Optimal",SUBSTITUTE(E3,E3,0),IF(E3="Normal",SUBSTITUT E(E3,E3,1) ,IF(E3="High-normal",SUBSTITUTE(E3,E3,2),IF(E3="mild",SUBSTITUTE(E3,E3,3),IF(E3 ="Moderate",SUBSTITUTE(E3,E3,4),IF(E3="Severe",SUBSTITUTE(E3,E3,5),IF(E3=TRUE,S U BSTITUTE(E3,E3,3)))))))),"")

But I've tried different ones, some quite simple and straight forward, including =SUM(F\$2:F8) right underneath the range F2:F8.

The data referred to by the IF function is a column of 6 categories (mentioned in the long formula above). The overriding NOT(ISBLANK() condition is because I only need this report in respect of every fourth row, differentiated by the day of the month in column A. What's interesting is that if I remove the final "" (leaving in the comma), I get a zero as the return - and, lo, the COUNT function loves that. It counts the number of zeros; the same, of course, if I manually insert digits. I tried making sure that the format of these source cells is set to number, also automatic.

The final test with "TRUE" is only necessary to avoid a "TRUE" return where no data has yet been entered, substituting the more or less norm of "3" as an interim placeholder - hardly relevant, I'm sure.

• Level 5 (7,855 points)

Hi Peter,

SUBSTITUTE is a text function, as shown by the definition in Function Browser:

(source-string, existing-string, new-string, occurrence)

1. source-string:
A string. source-string is a string value.
2. existing-string:
The string within the given string that is to be replaced. existing-string is a string value.
3. new-string:
The text used as a replacement for the section of the given string that is replaced. new-string is a string value. It does not have to be the same length as the text replaced.
4. occurrence:
An optional value specifying the occurrence that should be replaced. occurrence is a number value and must be greater than or equal to 1, or omitted. If greater than the number of times existing-string appears within source-string, no replacement will occur. If omitted, all occurrences of existing-string within source-string will be replaced by new-string.

Usage Notes

1. You can replace individual characters, whole words, or strings of characters within words.

Therefore it is placing a text value of zero.

AVERAGE should produce an error when applied to text

What you are getting is puzzling.

=IF(LEN(A)>0,IF(E3="Optimal",E3,0),IF( etc.

LEN greater than 0 detects a cell containing anything but null.

IF follows the logic:

IF(condition is true, then do this, else do that).

Regards,

Ian.

• Level 5 (7,855 points)

Hi Peter,

This may be what you want. VLOOKUP to another Table

Formula in G2 (and filled down) is:

=IF(F2=TRUE,VLOOKUP(E2,'Table 1-1' :: \$A:\$B,2,FALSE),"")

It finds the entry in Column E and returns the Level from Table 1-1 Column B

I couldn't get NOT(ISBLANK) to work, so I added Column F to check for entries in Column A. If F2=TRUE, then the VLOOKUP function is called, else ""

You can hide Column F.

Regards,

Ian.

• Level 1 (0 points)

Thank you Ian, very much. I had no idea how remote I was from attaining my goal. I see now how SUBSTITUTE was wrecking my traction and my account of what I was getting may be a little faulty, leading to the puzzle for you about what I was "getting".

Following your advice and using VLOOKUP, I've generated the running average I wanted. But now I've reached the next stage: to pick up the last running average and 'add it' to the formula in the adjacent column to feature there as its default return. It calculates the category that two prior data cells (in the row) fall into. Until such data is inserted the return is TRUE: by way of the default IF condition. It could readily be changed to FALSE, by inseting a NOT function. That probably doesn't matter, however.

If that default return is being given, I need to 'add' a reference to the cell to the right immediately above so that the return is the latest running average. And I need this factor to be automatically carried down to the end of the column.

It may make that easier to have the last running average figure repeated for all subsequent rows; then, no need to refer to the cell up-right, but sufficient to refer to the one to the right in the same row. That means that the first step would be to modify the running average formula to provde for it a default return and so obviate the present error return.

All this is to be able to produce two charts, one that displays the original data trend on the basis of a day's average (which is entered as a daily row), the second automatically updating, day by day, the levels that the raw data itself reveals. The reasons don't matter, but I still think it can most probably be done.

If all this is too hard to follow, just your first impression could be very helpful. I am extremely obliged to you for the help youv'e already given me. Many thanks.

Regards, Peter White

• Level 5 (7,855 points)

Hi Peter,

Here is a daily running average:

H2 = G2

H3 =IF(F3=TRUE,AVERAGE(H2,G3),"")

and fill down.

Select Column A, hold the command key down and select Column H, choose a chart (graph in Australia).

In Inspector > Chart > Axis > Category Axis (X) > Label every 7 categories (or the labels run over each other. I thought every Monday would do).

Regards,

Ian.

• Level 1 (0 points)

That was a real help, Ian. I didn't give you enough information for you to be able to see exactly where I was going, but you got me through the main difficulty just the same. My "running average" was needed to enable a new graph (based on the raw data in two columns) to provide a projection of the so far accumulated graph, at any point during a month, to its end — a feature that had several advantages. Unfortunately I don't have the skill you do to make my screenshot of the finished product available to you. But here are the formulas that worked.

=IF(ISBLANK(A),"",IF(F3=TRUE,F3,VLOOKUP(E3,Index::A:WHO graph level,2,0))). Index is what I called my second table.

The formula that gave TRUE or FALSE so the blank return really was an empty cell that did not spoil the COUNT function imbedded in AVERAGE, along the line you suggested, was =IFERROR(NOT(ISBLANK(A)),), with the initial cell being simply a copy of the source cell.

The formula that then provided me with a running average, so that when the point was reached where data was not yet available I could invoke the last available average for the month to provide a forward projection to the end of the current month, was, =IF(F3=TRUE,AVERAGE(G\$2:G3),"").

I was able to hide the two columns and consolidate the whole exercise onto one page. It satisfies a need that goes well beyond my need to keep track of my own blood pressure. So I'm most grateful for your help.

Regards, Peter White.

• Level 5 (7,855 points)

Hi Peter,

A screenshot will certainly help me and other users follow your solution.

To take a screen shot, hold down the shift and command keys, then type 4. The cursor will change to crosshairs. Release the shift and command keys. Drag over that part of your screen then release the mouse/trackpad. A screen shot will appear on your desktop. In a reply to a message, click on the camera icon in the Toolbar above your reply and Choose File.

You may have to try this twice. Camera icon sometimes needs a wake-up call, but works the second time.

BTW, I assumed you wanted a running average (today and yesterday updated each time you enter new data). There is also an accumulative average (from the start up to the current day).

I shall await your screenshot to understand. Remove personal details first

Regards,

Ian.

• Level 1 (0 points)

Hi Ian, again. I don't quite get the running average you refer to, but it doesn't matter. I think what I wanted and managed to obtain was what you call an accumulative average. You'll see that in the second graph in my screenshot the last four columns are just a projection of that average as accumulated from the beginning of the month. Exactly what I needed.

I'm now going to ***** the camera icon and add the screenshot...

FAILED. And although I've used Grab to get a screenshot of the toolbar above my reply, for the same reason I cannot include that either. I'm using a Mac. Is that why I don't have a "camera icon"? I do have an insert image icon and also one for inserting a Link. So I have wondered if the solution might be to drop the pic ifile nto Dropbox and provide a link to it there. Any advice?

Regards, Peter White

• Level 5 (7,855 points)

Hi Peter,

The camera icon is in the toolbar above a reply in these forums. You need to have an active reply box to see it (it doesn't appear in replies that have been posted). Yes, it works on a Mac.

Here is the toolbar that is visible as I compose this reply:

(This is getting a bit like Russian dolls!)

The camera icon is this one:

Remember you may have to try this twice. Camera icon sometimes needs a wake-up call, but works the second time.

Regards,

Ian

• Level 7 (29,470 points)

HI Peter,

I've been following this conversation off and on for the past few days, and have a few questions/comments.

Screen shots: Grab is OK, but I find the procedure described above by Ian simpler (and more famiiar, since it's been part of the Mac OS snce before Grab was even thought of). Here's a review:

1. Make sure what you want a shot of is on the screen.

2. Place the mouse pointer at one corner of the area you want to include in the shot.

3. Press shift-command-4 (The mouse pointer will change to a crosshair.)

4. Press and hold the mouse button; Drag a selection rectangle to enclose the area you waant in the shot.

5. When the rectangle encloses your well-composed shot, release the mouse button.

The screen shot s now savd to your Desktop, with the title "Screen Shot..."followed by the date and time.

To post the sreen shot:

1. Click the camera icon in the bar above the compose window:
2. In the dialogue that opens, Click Choose File.

3. Click the fine name in the Choose window. Click Choose.

4. The Choose window cloes. Click Insert File.
(See Ian's note regarding having to repeat this procedure on the first attempt.)

Done.

Formulas:

"=IF(ISBLANK(A),"",IF(F3=TRUE,F3,VLOOKUP(E3,Index::A:WHO graph level,2,0)))"

F3 contains the result of a test for A3 being not blank. Since F3 will always be the opposite of the first IF's condition, this test is redundent, and can be romoved (as can the whole of column F).

Revised formula:

=IF(ISBLANK(A),"",VLOOKUP(E3,Index::A:WHO graph level,2,0))

OR

=IF(LEN(A)<1,"",VLOOKUP(E3,Index::A:WHO graph level,2,0))

OR

=IF(LEN(A)>0,VLOOKUP(E3,Index::A:WHO graph level,2,0),"")

"=IFERROR(NOT(ISBLANK(A)),)"

This is the formula in column F, and may be eliminated.

"=IF(F3=TRUE,AVERAGE(G\$2:G3),"")"

With column F eliminated. this needs a simple replacement of the F3=TRUE test with the test that made F3 "TRUE"

=IF(LEN(A3)>0,AVERAGE(G\$2:G3),"")

You mentioned that this formula "provide(d) a forward projection to the end of the current month," which it does not.

If you want it to repeat the last average to the end of the month, make this change:

=IF(LEN(A3)>0,AVERAGE(G\$2:G3),G3)

While this will give you a stright line across th rest of the graph, the line is pretty meaningless. I would eliminate it as misleading.

Here's the result, with a chart showing both daily values and the cumulative average to each day.

Regards,

Barry

• Level 1 (0 points)

Here it is, Ian. Fortunately I've worked around rather than pursued or tinkered with the Russian dolls. Once I knew that the "Insert image" icon was what you were pointing me too, I persevered and (eventually) the Choose option emerged.

You can see that the data has been entered for all but the last 4 days of the month (top graph) and that in the lower graph the last 4 days are all the same — because they're the projection of an average accumulated to date. That's just what I wanted.