Round formula containing text

[Ironman]

Hi

Cell F1 contains a heading with a formula in the format "COMMENTS (0.000000000000% CONVERTED)".

The formula is

="COMMENTS ("& ((MAX(IF(B2:B6210<>"",ROW(2:6210))))/6210)*100 & "% CONVERTED)"

and divides the last used row number in Col B by 6210.

All I'm looking for is a way to round the formula result to the nearest whole number instead of 12 decimal places e.g. COMMENTS (38% COMPLETED). I can't use ROUND because I get a circular reference and using number format makes no difference.

Many thanks!

 

[JoeMo]

Maybe:

="COMMENTS ("&ROUND(((MAX(IF(B2:B6210<>"",ROW(2:6210))))/6210)*100,0) & "% CONVERTED)"

 

[bebo021999]

Change it to: (Ctrl-Shift_enter for Ex2016 or earlier)

="COMMENTS ("& ROUND(((MAX(IF(B2:B6210<>"",ROW(2:6210))))/6210)*100,0) & "% CONVERTED)"

Or faster with alternative non-array version (Enter only)

="COMMENTS ("& ROUND(LOOKUP(2,1/(B2:B6210<>""),ROW(B2:B6210))/6210,0)& "% CONVERTED)"

 

[Ironman]

Many thanks Joe and bebo, that worked perfectly!
(@Bebo - your non-array version returned 0%)

 

[bebo021999]

Many thanks Joe and bebo, that worked perfectly!
(@Bebo - your non-array version returned 0%)

Opp. I forgot multifly with 100
="COMMENTS ("& ROUND((LOOKUP(2,1/(B2:B6210<>""),ROW(B2:B6210))/6210)*100,0)& "% CONVERTED)"

 

[Ironman]

="COMMENTS ("& ROUND((LOOKUP(2,1/(B2:B6210<>""),ROW(B2:B6210))/6210)*100,0)& "% CONVERTED)"

Yes, that works fine too - thanks again bebo!

 

[Ironman]

While you're there, can I just ask if there's a way I can create a link to this part of the formula

LOOKUP(2,1/(B2:B6210<>""),ROW(B2:B6210))/6210

i.e. go to the first empty cell in the column?

 

[bebo021999]

While you're there, can I just ask if there's a way I can create a link to this part of the formula

LOOKUP(2,1/(B2:B6210<>""),ROW(B2:B6210))/6210

i.e. go to the first empty cell in the column?

Nope. Go to last non-empty cell then get the row index

 

[Ironman]

OK thanks - I'll post separately for a VBA solution with a selection_change event, which I think should be possible.