In the cells they will be entering this negative number:
Data--Validation
Allow: Decimal
Data: Less than or equal to
Maximum: 0

NOTE: You can change DATA to Less than as needed.

--
John C


"Courtney" wrote:

I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!

Thanks for the reply - that works if they enter a negative number, but can
you also make it to where if they enter it as a positive, instead of getting
an error message it automatically converts it to a negative number on what
they entered. Ex. If they entered an expense as $600 it would convert to
($600).

Thanks

"John C" wrote:

In the cells they will be entering this negative number:
Data--Validation
Allow: Decimal
Data: Less than or equal to
Maximum: 0

NOTE: You can change DATA to Less than as needed.

--
John C


"Courtney" wrote:

I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!

May I suggest a different approach.
Assumptions, columns B, D, and F have revenue, columns C, E, and G have
expenses.
Select all cells (that will have a revenue or expense entered).
Data--Validation, Decimal, Greater than or Equal to, 0
Select the expense cells, go to Menu: Format--Cells--Number--Custom
I used the following:
[Red](#,##0.00)
A format for negative numbers is not needed, as we don't allow entry of
negative numbers.

Assuming Row 2 is the first set of data of expenses and revenues, my total
formula entered in cell H2 is as follows:
=SUM(B2,D2,F2)+SUM(C2,E2,G2)*-1

Then Edit--Fill--Down as needed.
--
John C


"Courtney" wrote:

Thanks for the reply - that works if they enter a negative number, but can
you also make it to where if they enter it as a positive, instead of getting
an error message it automatically converts it to a negative number on what
they entered. Ex. If they entered an expense as $600 it would convert to
($600).

Thanks

"John C" wrote:

In the cells they will be entering this negative number:
Data--Validation
Allow: Decimal
Data: Less than or equal to
Maximum: 0

NOTE: You can change DATA to Less than as needed.

--
John C


"Courtney" wrote:

I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!

Thanks - I think this would be the same effect of changing all my
totals/formulas to automatically subtract expenses entered as positive
numbers - that might be the answer - was trying to look for an automatic way
to convert to a negative number to prevent some re-work, but might be
unavoidable. Appreciate the help though.

"John C" wrote:

May I suggest a different approach.
Assumptions, columns B, D, and F have revenue, columns C, E, and G have
expenses.
Select all cells (that will have a revenue or expense entered).
Data--Validation, Decimal, Greater than or Equal to, 0
Select the expense cells, go to Menu: Format--Cells--Number--Custom
I used the following:
[Red](#,##0.00)
A format for negative numbers is not needed, as we don't allow entry of
negative numbers.

Assuming Row 2 is the first set of data of expenses and revenues, my total
formula entered in cell H2 is as follows:
=SUM(B2,D2,F2)+SUM(C2,E2,G2)*-1

Then Edit--Fill--Down as needed.
--
John C


"Courtney" wrote:

Thanks for the reply - that works if they enter a negative number, but can
you also make it to where if they enter it as a positive, instead of getting
an error message it automatically converts it to a negative number on what
they entered. Ex. If they entered an expense as $600 it would convert to
($600).

Thanks

"John C" wrote:

In the cells they will be entering this negative number:
Data--Validation
Allow: Decimal
Data: Less than or equal to
Maximum: 0

NOTE: You can change DATA to Less than as needed.

--
John C


"Courtney" wrote:

I'm building a model where end-users must input expenses and revenues. In
the expense section I'd like to ensure that numbers are entered as negatives,
or if they are entered as positives build a formula in the data validation
section that would automatically convert them to negative numbers. Any idea
on how to do this?

Any insight would be appreciated.

Thanks!