Adding a value to an cell that has no value but has a formula

Occasional Contributor

Hi Team,

wondering if there is a way to put a value say "1000' into a cell that is empty (no value) but has a formula in it, and ignores it if it does have a number in it...any offers of assistance would be greatly appreciated..



6 Replies


Make the formula return 1000 instead of 0 or "-" (I don't know what it currently returns in the marked cells)

Hi Hans,
The formula is looking at another cell and if that cell returns no value then i would like to add the "1000" into that cell, hope that makes sense....


Sorry, I don't understand. Can you explain in more detail?

@Hans Vogelaar Hi Hans,
i have used a choose(match function looking at four columns, and it picks the values in the quantity breaks and should return the break $ value when the qty is added however we have to few values the $ value does not show or is incorrect until i add more values to overcome this issue i have attached the sheet for reference for you to see what i am doing as i find it difficult to explain it in more detail best to see for your self....highlighted cells for reference and some notes to explain via text box, sorry i am bit new to xcel so apologies 


I don't understand why cell AB5, under the text "QB2", refers to cell N5, under the text "QB1", etc.

But perhaps the attached version will do what you want.

best response confirmed by Marcel55 (Occasional Contributor)
Hi Hans,
Your a rock star thanks for this, i only built the AB5 etc to group the data together but your method is far better and works so thanks so much for taking the time to look at this for me its much appreciated.