=if(b4 is a whole number, multiply by x,y)

[RoyS]

In my job estimation spreadsheet i want to have the option to enter a whole number or a decimal number in a given cell and have one single formula that will produce a cost. For example if i enter "20" in cell b4 or if i decide to enter "2.3" in the same cell b4 ( one or the other) the result in b5 will be the correct cost as multiplied be either the $60.00 hr or $1.00 min. I am looking for one single formula. I could not find the thread and wasn't sure how to word the search option. Thank you Roy

 

[T. Valko]

In my job estimation spreadsheet i want to have the option to enter a whole number or a decimal number in a given cell and have one single formula that will produce a cost. For example if i enter "20" in cell b4 or if i decide to enter "2.3" in the same cell b4 ( one or the other) the result in b5 will be the correct cost as multiplied be either the $60.00 hr or $1.00 min. I am looking for one single formula. I could not find the thread and wasn't sure how to word the search option. Thank you Roy

OK, so what determines whether you multiply by $60.00 or multiply by $1.00?

 

[Rick Rothstein]

I think you need to supply some additional information. My guess is that the 20 is a number of minutes and the 2.3 is a number of hours. If that guess is correct, and given what you want to do, how would you enter 2 minutes into the cell? Where is the breakpoint where a number stops being consider minutes and should be considered hours?

 

[RoyS]

if i want to build a sign and decide to estimate in hours instead of minutes , the 2.3*60(estimate by hours). If i enter that the time will be only 30 minutes then , 30*1 (estimate by minutes). When i figure costs to build a sign i have found it much more efficient to have the option of decimal or whole number. I don't want to be restricted to minutes with the estimate and have to figure , gee this will take many, many hours, so how many hours will that equal in minutes? I want to be able to say this job will take 6.3 hours and just plug in a decimal if i want (6.3*60). Did that answer the question? Not sure f i began to ramble. Note $60 = hourly rate. $1.00 = minute rate of $60 hr.
Thanks

 

[RoyS]

Rick - thanks for reading the thread. I don't have jobs that take only 2 minutes. The minumum charge we work for is $75.00. Thanks

 

[T. Valko]

if i want to build a sign and decide to estimate in hours instead of minutes , the 2.3*60(estimate by hours). If i enter that the time will be only 30 minutes then , 30*1 (estimate by minutes). When i figure costs to build a sign i have found it much more efficient to have the option of decimal or whole number. I don't want to be restricted to minutes with the estimate and have to figure , gee this will take many, many hours, so how many hours will that equal in minutes? I want to be able to say this job will take 6.3 hours and just plug in a decimal if i want (6.3*60). Did that answer the question? Not sure f i began to ramble. Note $60 = hourly rate. $1.00 = minute rate of $60 hr.
Thanks

Try this...

=B4*IF(MOD(B4,1),60,1)

 

[RoyS]

T Valko - thank you. at first i thought it didn't work, however i realized that the cell had to be blank for your formula to work. Thanks again. Roy

 

[RoyS]

How does the formula change if the $60 hr rate is a result of a choice from a dropdown list. My rates vary according to the task. 92,75,60,45,30. A vlookup in the next column beside the dropdown list displays the per minute cost.

time product cost hourly rate minute rate
20 ? $60.00 $1.00

or

2.3 ? $45.00 $.75

 

[RoyS]

How does the formula change if the $60 hr rate is a result of a choice from a dropdown list. My hourly rates vary according to the task,$92,$75,$60,$45,$30. A vlookup in the next column beside the dropdown list displays the per minute cost.

time product cost hourly rate minute rate
20 ? $60.00 $1.00

or

2.3 ? $45.00 $.75

 

[Rick Rothstein]

Assuming Column D contains the rate per minute and Column E contains the hourly rate, Biff's formula would become..

=B4*IF(MOD(B4,1),E4,D4)

Note all I did was replace his 60 with the column/row reference holding the 60 and the 1 with the column/row reference holding it.