getting two formulas to work together in same cell

**Crasher** · 10-10-2006, 03:19 PM

i was advised to repost explaining my dilema properly. here goes.

i have the following formulas that work perfectly seperatly

=MAX(0,(E28-D28-"0:45")*24-F28)

=IF(C28=$C$70,C28=$C$72),2,0)

but it is imperative that i get them to show there results in the same cell.

neither are used at the same time, only one result is required depending on what is needed.

i did try putting them together as follows but did not work

=MAX(0,(E28-D28-"0:45")*24-F28),IF(C28=$C$70,C28=$C$72),2,0)

formula required please.

**SteveG** · 10-10-2006, 03:48 PM

Crasher,

Sounds like you need to incorporate your 2 formulas into an IF statement to determine which to use. Something like:

=IF(E1=1,MAX(0,(E28-D28-"0:45")*24-F28),IF(AND(C28=$C$70,C28=$C$72),2,0))

Where "=IF(E1=1" will determine which formula is used. If it is TRUE the MAX formula will be used, if false the IF formula.

HTH

Steve

**VBA Noob** · 10-10-2006, 03:51 PM

What cell do you want the result

Also this formula doesn't work

=IF(C28=$C$70,C28=$C$72),2,0)

is it a or formula ??

VBA Noob

**Crasher** · 10-10-2006, 03:58 PM

Originally Posted by VBA Noob

What cell do you want the result

Also this formula doesn't work

=IF(C28=$C$70,C28=$C$72),2,0)

is it a or formula ??

VBA Noob

this formula does work because ive used it on my work sheet

**SteveG** · 10-10-2006, 03:58 PM

Crasher,

I changed your IF formula to include an AND as VBA Noob points out, the original format wouldn't work.

HTH

Steve

**Crasher** · 10-10-2006, 04:03 PM

id like to suggest that you download my worksheet and see if you can see what i mean.

i need the results to show in colum 'G'

see the first part of the formula relates to start and finish times colum 'D' and 'E' and works out how much overtime i have got in time.

and...

the second part of the formula relates to colum 'bankholiday' 'holiday' these two are related to cells 'c70' and 'c72'. hope that helps, because if in colom 'c' either 'bankholiday' 'holiday' i need the number '2' to be put into colom 'g'.

**SteveG** · 10-10-2006, 04:14 PM

Crasher,

Again to VBA Noob's point the formula as posted does not work (I could not find it in your spreadsheet anywhere) but in looking at your sheet it appears that you want the value in G to be 2 if C28 is either "Holiday" or "BankHoliday" and if not then do the MAX calculation so try this.

=IF(OR(C28=$C$70,C28=$C$72),2,MAX(0,(E28-D28-"0:45")*24-F28))

HTH

Steve

**Crasher** · 10-10-2006, 04:23 PM

Originally Posted by SteveG

Crasher,

Again to VBA Noob's point the formula as posted does not work (I could not find it in your spreadsheet anywhere) but in looking at your sheet it appears that you want the value in G to be 2 if C28 is either "Holiday" or "BankHoliday" and if not then do the MAX calculation so try this.

=IF(OR(C28=$C$70,C28=$C$72),2,MAX(0,(E28-D28-"0:45")*24-F28))

HTH

Steve

just what the doctor order you are a star. it works great.

many thanks.

**Crasher** · 10-10-2006, 04:31 PM

SteveG can i ask you one other question please?!

if you look on the saturday and sunday 'g' colom please. i need to show either '4' (represents upto 4hrs work) or '8' (represents between 4.25 to 8hrs work) how ever if over 8.75 (.75 represents 45min) then deduct .75

=IF(MAX(0,(E10-D10)*24)<=4,MAX(0,(E10-D10)*24),MAX(0,(E10-D10-"0:45")*24))

can you modify this formula please.

many thanks

**Crasher** · 10-10-2006, 04:44 PM

darn i just spotted something else.

at the moment colom 'I' correctly calculates at 1.5* 8.9654 (which works out at £13.4481).

formula read =G6*$I$4

your formula that you just did for me works great (very happy)

how ever can you do to this formula as to what you did with the post above but do the cost instead.

at present when the '2' gets entered into colom 'g' its times it by 1.5 and i need it to times 'g' by 'h4' on entering 'holiday' or 'bankholiday'

sorry to sound confusing.