Formula help

R

rhombus4

Well-known Member
Joined
May 26, 2010
Messages
586
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have numbers which are formatted with no decimal places. i.e. 13/4 =3these are in column A, what I need in column B is a formula which is A*5 but will show 15 not 3.25*5 and also format it in time units of 5 minse.g. if A = 3.25 which will show as 3 then column B will show 15 mins shown as 0.15
 
Sort by date Sort by votes
actually managed to solve the first part.I formatted the initial cell =ROUNDUP(c4/4,0) but still have the problem if over 0.6 e.g. 13/20 =0.65 need it to be 1.05
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
VoG said:
Try

=IF(A1<0,"",INT((ROUNDUP(A1,0)/20)/0.6))+MOD(ROUNDUP(A1,0)/20,0.6)
Click to expand...

</p> for negative figures I get Value error and for 0's i get 0</p></p> In column C the formula is now =ROUNDUP(M4/4,0) </p></p> Cell D is always a whole number </p></p> column E is D-C </p></p> and A is now E/20 </p>
 
Upvote 0
VoG said:
Try

=IF(A1<0,"",INT((ROUNDUP(A1,0)/20)/0.6))+MOD(ROUNDUP(A1,0)/20,0.6)
Click to expand...

it does solve the problem when over 0.60 eg. 0.65 shows as 1.05 but dont understand why the first part doesnt work if A1<=0,"", should be blank but value error???????
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Question Question
Show Decimals until first non-zero decimal is shown
2
Replies
13
Views
402
myall_blues
myall_blues
E
  • Question Question
Negative times and conditional formating.
Replies
2
Views
479
Ludwig
L
M
  • Locked
  • Question Question
Trying to input time calculation so it does not change to 24 hour format
Replies
3
Views
205
Peter_SSs
Peter_SSs
B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
563
bh24524
B
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
302
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,225,204
Messages
6,183,581
Members
453,172
Latest member
dreximgirl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top