Format Currency in-cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lyners - 24 Sep 2007 17:50 GMT

I have a need to format cells as currency, but have to do it in-cell. This is
just like =DOLLAR() except I need it to be a currency so that it can be
summed. I have tried a lot of different steps, but cannot get the cell to
format like currency without turning it into text. I have to do this in-cell.
I cannot use format->cell->currency.

Any help is appreciated.

Thank you.

Reply to this Message

JE McGimpsey - 24 Sep 2007 18:04 GMT

If the value is a number, so that it can be summed, it will be displayed
in accordance with the cell's display format (e.g.,
Format/Cell/Currency).

OTOH, you can coerce Text values (e.g., the result of DOLLAR()) to
numbers in your Sum function. For instance, if your Text values are in
B1:B15, array-enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(--B1:B15)

> I have a need to format cells as currency, but have to do it in-cell. This is
> just like =DOLLAR() except I need it to be a currency so that it can be
> summed. I have tried a lot of different steps, but cannot get the cell to
> format like currency without turning it into text. I have to do this in-cell.
> I cannot use format->cell->currency.

Reply to this Message

Lyners - 24 Sep 2007 18:22 GMT

Hi JE,
What if my cell that I summing looks like this:

=SUMIF(E1:INDIRECT(ADDRESS(ROW()-1,5)),"Revenues",F1:INDIRECT(ADDRESS(ROW()-1,6)))-SUMIF(F1:INDIRECT(ADDRESS(ROW()-1,5)),"Expenditures",F1:INDIRECT(ADDRESS(ROW()-1,6)))

Do I put "--" in front of the F1?

My Revenues and Expenditures Cells look like this:

=DOLLAR(SUMIF(D3:INDIRECT(ADDRESS(ROW()-1,4)),"",F3:INDIRECT(ADDRESS(ROW()-1,6))))

I want to sum up the values so that they look like currency. I am doing this
through a third party datagrid and that is why I have to use formulas to
format the cell.

Is there a way to do this?

Thank you

> If the value is a number, so that it can be summed, it will be displayed
> in accordance with the cell's display format (e.g.,
[quoted text clipped - 11 lines]
> > format like currency without turning it into text. I have to do this in-cell.
> > I cannot use format->cell->currency.

Reply to this Message