# Division to exclude input value



## Huizar (Dec 21, 2022)

Hi Excel Fans,

I have been racking my brain probably for an easy formula, which I can't get to work. I'm looking for a formula that will give me the percent for each item based on Totals, the caveat is let's say someone manually makes an adjustment to the value column, i want to now only account for the cells that have formulas only.  Hence my new Output column. Anyone that could help would be greatly appreciated. 



ValueRegular Output %New OutputNotes4026.7%0%40 is a manual Input5033.3%45.5%50 is formula6040%54.5%60 is a formulaTotal 150100%100%


----------



## StephenCrump (Dec 22, 2022)

It's not clear what your results are meant to be?

But you can use =ISFORMULA(reference) to determine whether a cell has a formula or a value, and define your formula appropriately:

AB1100502804036060 is a manual input4Sheet1Cell FormulasRangeFormulaA1A1=100A2A2=80B1:B3B1=IF(ISFORMULA(A1),50%*A1,A1 & " is a manual input")


----------



## Huizar (Dec 22, 2022)

StephenCrump said:


> It's not clear what your results are meant to be?
> 
> But you can use =ISFORMULA(reference) to determine whether a cell has a formula or a value, and define your formula appropriately:
> 
> AB1100502804036060 is a manual input4Sheet1Cell FormulasRangeFormulaA1A1=100A2A2=80B1:B3B1=IF(ISFORMULA(A1),50%*A1,A1 & " is a manual input")


Hi,

Sorry for not being clear. What i'm trying to do is get the percent for each line item of the total. So Let's say someone types in the 40 hours with their keyboard, i want to ignore the 40 and allocate the new amounts based on Leaks and Drilling because they are formula driven. I put the note column to kind of give an idea of what i want to do. I want the New Percent of total output. I put to get a better idea. I really appreciate your help on this. 

Thanks.


ItemsHoursPercent of Total*New Percent of Total*NoteObservations4023.5%0%Since 40 Hours is manual input by someone, i want to ignore it as part of the total. Leaks5029.4%38%New Distribution of the 50/(50+80) Drilling8047.1%62%New Distribution of the 80/(50+80)Total170100.0%100%


----------



## StephenCrump (Dec 22, 2022)

Huizar said:


> Sorry for not being clear ...


Much clearer now, thanks.

Something like this perhaps:

ABCD1ItemsHoursPercent of TotalRevised %2Observations4023.53%Nil - ignore manual entry3Leaks5029.41%38.46%4Drilling8047.06%61.54%5Total170100.00%100.00%6Revised total130Sheet1Cell FormulasRangeFormulaC2:C4C2=B2/B$5D2:D4D2=IFERROR(IF(ISFORMULA(B2),B2/B$6,"Nil - ignore manual entry"),"-")C5:D5C5=SUM(C2:C4)B3B3=25+25B4B4=2*40B6B6=SUMPRODUCT(IF(ISFORMULA(B$2:B$4),B$2:B$4))


----------

