What formula to use??

[Seymore]

I have a range of numbers (months) and text
1
2
3
4
5
6
7
8
9
10
11
12
One Time
Each number is for each month but I need to add the option of "One Time" , One time should equal to 1.
So I ask the question to calculate savings for the rest of the year, the formula follows
(Y9/12)*(13-J9) where Y9N is a fixed number , lets say 100 and JP is the month lets say 3
In this case it would be (100/12)*(13-3)=83.33
I would like to add the option to choose One Time and it will multiplyit times 1 as if J9=1.
How can I do that?
Thanks

 

[SirJB7]

Hi, Seymore!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, you have 2 choices, at least. The regular one and the tricky one.

The regular one is to change your formula to something like:
=(Y9/12)*(13-IF(ISNUMBER(J9),J9,1))

The tricky one, which I prefer, is keeping your original formula, putting a 1 in cell J9, and format it as "One time".

Regards!

 

[Hui]

Seymore

You could also use a Custom Number Format
In the cell where you want "One Time", simply type 1
Then apply a Custom Number Format of "One Time" including the quotes
The cell will display "One Time" but it will appear to calculations or other cells as 1
So your formula stays as =(Y9/12)*(13-J9)

 

[Seymore]

Thank you both so much, problem solved, I literally spent 2 hours trying to figure this thing out, I really appreciate the help.

Cheers

 

[SirJB7]

Hi, Seymore!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!

@Hui
Hi, buddy!
I assume you didn't read me.
Regards!
PS:

You could also use a...