Merging Excel Formulae

[Airbiskit]

Hi,

In column L4 I have the formula =SUM(24-H4+K4)
In the next column I have the formula =IF(L4>24,L4-24,L4)

Is there anyway of merging these 2 formulas so I don't need the extra column?

Many Thanks

Steve

 

[xlbo]

=IF(SUM(24-H4+K4)>24,L4-24,L4)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Breaking long formulas down into multiple rows is a great way for beginners to work through the logic of what they want to do. I suggest starting in one column, then working left for each additional "layer" of the formula you want to create.

Once you have everything finished, just work backwards from right to left, replacing each cell reference with the formula contained in that cell (minus the equals sign).

In your case:

In M4, you have
[tab]=IF([red]L4[/red]>24,[red]L4[/red]-24,[red]L4[/red])
which refers to L4 three times.

L4, in turn, contains the formula
[tab]=SUM(24-H4+K4)

so every time "L4" appears in M4's formula, you can just copy 'n' paste L4's formula (without the "=").

M4 will then look like this:
[tab]=IF([red]SUM(24-H4+K4)[/red]>24,[red]SUM(24-H4+K4)[/red]-24,[red]SUM(24-H4+K4)[/red])

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

ooops - missed the other "L" references. Thanks for the catch John

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

no problem.

Er, I just reread my post. Apparently i wasn't quite awake yet. The first paragraph should read:

Breaking long formulas down into multiple columns is a great way for beginners to work through the logic of what they want to do. I suggest starting in one column, then working right for each additional "layer" of the formula you want to create.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Breaking long formulas down into multiple columns is a great way for beginners to work through the logic of what they want to do.

Then say "Hello, Skip!" to a "beginner". ;-)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Airbiskit]

Wonderful, thats works a treat. Thank you.

 

[Markus4]

I'm a little late, but how about

=(24-H4+K4)-IF((24-H4+K4)>24,24,0)

Markus
...who has thought of OneDTenT's motto...